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.
I need to create a matrix of revenue by month and year, with each year showing a running total. Essentially:
I have a date table linked to my sales data and created the following measure for cumulative sales:
Cumulative = CALCULATE(sum('TTM Data'[Extended Price]), filter(ALL('Date Table'[Date]), 'Date Table'[Date]<=max('Date Table'[Date])))
When I use the date field from my date table as the row and column fields, I get a matrix that does not reset the cumulative total at each year:
How should I write the Cumulative measure to correct this and give me what I want?
Thanks in advance for the help!
Solved! Go to Solution.
Try like
Totalytd Sales = CALCULATE(TOTALYTD(sum(TTM Data[Extended Price]),'Date Table'[Date],"12/31"))
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.
My Recent Blog - https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601
Hi,
To your visual, drag Year and Month from the Date Table. Write these measures
EP = sum('TTM Data'[Extended Price])
EP YTD = CALCULATE([EP],DATESYTD('Date Table'[Date],"31/12"))
Drag the second measure to the visual.
Hope this helps.
Try like
Totalytd Sales = CALCULATE(TOTALYTD(sum(TTM Data[Extended Price]),'Date Table'[Date],"12/31"))
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.
My Recent Blog - https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601
Hi @ef3f ,
I think what you need is the TOTALYTD formula: https://docs.microsoft.com/en-us/dax/totalytd-function-dax
In your formula, you are filtering all dates earlier than the current date.
This means that in March 2019 you will include Jan, Feb 2019 but also all months of 2018 and 2017.
TOTALYTD should do exactly what you want.
Let me know if this helps you.
LC
Interested in Power BI and DAX templates? Check out my blog at www.finance-bi.com
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 |
---|---|
108 | |
98 | |
78 | |
66 | |
53 |
User | Count |
---|---|
139 | |
100 | |
95 | |
85 | |
63 |