在PowerBI中计算每月余额

Question

数据表：
DB Fiddle

CREATE TABLE vouchers (
id SERIAL PRIMARY KEY,
event_date DATE,
credits_collected INT,
credits_redeemed INT
);
INSERT INTO vouchers
(event_date, credits_collected, credits_redeemed
)
VALUES 
('2020-01-08', '900', '700'),
('2020-02-15', '500', '300'),
('2020-02-20', '100', '250'),
('2020-03-19', '600', '850'),
('2020-04-03', '450', '130');

---

SQL查询：

SELECT
t1.event_month AS event_month,
t1.credits_collected AS credits_collected,
t1.credits_redeemed AS credits_redeemed,
SUM(t1.credits_collected - t1.credits_redeemed) OVER (
ORDER BY t1.event_month ASC ROWS UNBOUNDED PRECEDING) AS balance
FROM
(SELECT
DATE_PART('month', v.event_date) AS event_month,
SUM(v.credits_collected) AS credits_collected,
SUM(v.credits_redeemed) AS credits_redeemed
FROM vouchers v
GROUP BY 1
ORDER BY 1) t1
GROUP BY 1,2,3
ORDER BY 1;

---

结果：

event_month  |  credits_collected  |  credits_redeemed  |  balance
-------------|---------------------|--------------------|------------
1        |        900          |       700          |     200
2        |        600          |       550          |     250
3        |        600          |       850          |       0
4        |        450          |       130          |     320

---

我正在将上述data-table加载到PowerBI中
现在，我想创建一个报告，它看起来像我使用上面的SQL查询得到的结果

我能够将credits_collected和credits_redeemed添加到报告中
然而，我不知道我需要什么DAX公式来计算每个月底的余额

你知道我该怎么解决这个问题吗？

Answer 1

我可以通过两个步骤来解决问题：

步骤1:使用此DAX公式实现附加列：

column_balance_calculated_daily = CALCULATE(SUM(Tabelle1[credits_collected])-SUM(Tabelle1[credits_redeemed]),ALL('Tabelle1'),'Tabelle1'[event_date]<=EARLIER('Tabelle1'[event_date] ))

步骤2:使用以下DAX公式中的列：

balance = CALCULATE(MAX(Tabelle1[column_balance_calculated_daily]),ENDOFMONTH(Tabelle1[event_date]))