Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello all,
I have been through various answers on this page but non of them could help me so far.
The reqest is to show a dynamic running cumulative total that could be sliced with date slicers (year, month etc.).
The following formula works but it calculates the cumulative total from the first day in the table and does not take into account the date slicer:
GSV RT =
VAR MinDate = MIN('Accounting Date'[Accounting Date])
VAR MaxDate = MAX('Accounting Date'[Accounting Date])
RETURN
CALCULATE(
[GSV],
FILTER(
ALLSELECTED('Accounting Date'[Accounting Date]),
'Accounting Date'[Accounting Date] <= MaxDate
)
)
- when the year slicer is applied, the first day contains the cumulative total of the past years (30,000 in the table below) as well so it doesn't start from 0
Date | GSV | GSV RT Current | GSV RT Correct |
1/1/2021 | 1 | 30,001 | 1 |
2/1/2021 | 100 | 30,101 | 101 |
3/1/2021 | 100 | 30,201 | 201 |
4/1/2021 | 200 | 30,401 | 401 |
When I use the formula below, the cumulative sum stops working alltogether and the data is shown as if I used the SUM(Sales table [GSC]) instead:
GSV RT =
VAR MinDate = MIN('Accounting Date'[Accounting Date])
VAR MaxDate = MAX('Accounting Date'[Accounting Date])
RETURN
CALCULATE(
[GSV],
FILTER(
ALLSELECTED('Accounting Date'[Accounting Date]),
'Accounting Date'[Accounting Date] <= MaxDate &&
'Accounting Date'[Accounting Date] >= MinDate
)
)
Date | GSV | GSV RT Current | GSV RT Correct |
1/1/2021 | 1 | 1 | 1 |
2/1/2021 | 100 | 100 | 101 |
3/1/2021 | 100 | 100 | 201 |
4/1/2021 | 200 | 200 | 401 |
I have tried using 'ALL' instead of 'ALLSELECTED' as well but with no change whatsoever.
Any advice is greatly appreciated.
Of course ALL and ALLSELECTED in this case return the same results. If you want to know why... you'll have to read this: The definitive guide to ALLSELECTED - SQLBI
Also, it really does matter which columns you use in your formulas, which columns have filters placed on them and what the relationships are. It really does matter.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
43 | |
23 | |
21 | |
15 | |
15 |
User | Count |
---|---|
46 | |
31 | |
30 | |
18 | |
18 |