Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Good afternoon all.
I've been looking for a solution to this and tried many of the solutions already mentioned, but these all only seem to work with SET, framed data.
What I am trying to achieve (in my head) sounds like it should be a pretty standard requirement for most organisations.
Example:
ItemNumber | Quantity | Sales Invoice Date | Sales Year Month |
ProdA | 5 | 2 Jan 2022 | 2022-01 |
ProdA | 20 | 5 Jan 2022 | 2022-01 |
ProdB | 10 | 6 Jan 2022 | 2022-01 |
ProdB | 12 | 10 Feb 2022 | 2022-02 |
Prod A | 30 | 15 Feb 2022 | 2022-02 |
Prod C | 100 | 20 Feb 2022 | 2022-02 |
Prod C | 25 | 25 Feb 2022 | 2022-02 |
Prod C | 30 | 4 Mar 2022 | 2022-03 |
Prod A | 40 | 7 April | 2022-04 |
So if no filter is applied, it the line chart should look like:
Sales Year Month | Cumulative Sales Qty |
2022-01 | 35 |
2022-02 | 35+167 = 202 |
2022-03 | 35+167+30 = 232 |
2022-04 | 35+167+30+40 = 272 |
However, if they selected Date Range 2022-03 to 2022-04 the chart would show
Sales Year Month | Cumulative Sales Qty |
2022-03 | 30 |
2022-04 | 30+40 = 70 |
And if they just selected Product A and no date filter, the chart would show
Sales Year Month | Cumulative Sales Qty |
2022-01 | 25 |
2022-02 | 25+30 = 55 |
2022-03 | 25+30+0 = 55 |
2022-04 | 25+30+0+40 = 95 |
Non of the solutions I've found regarding cumulative calculations, allows for this level of flexability.
Please help.
Regards
Neil
Solved! Go to Solution.
Hi,
I suggest having a calendar table like below.
Please check the below picture and the attached pbix file.
Cumulative sales qty: =
CALCULATE (
SUM ( Data[Quantity] ),
FILTER (
ALLSELECTED ( 'Calendar' ),
'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
)
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi,
I suggest having a calendar table like below.
Please check the below picture and the attached pbix file.
Cumulative sales qty: =
CALCULATE (
SUM ( Data[Quantity] ),
FILTER (
ALLSELECTED ( 'Calendar' ),
'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
)
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.