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.
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.
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 |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |