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 help creating a DAX measure for the following task in the below dataset
Year | Month | Sales |
2019 | Jan | 100 |
2019 | Feb | 110 |
2019 | Mar | 120 |
2019 | Apr | 130 |
2019 | May | 140 |
2019 | Jun | 150 |
2019 | Jul | 160 |
2019 | Aug | 170 |
2019 | Sep | 180 |
2019 | Oct | 190 |
2019 | Nov | 200 |
2019 | Dec | 210 |
2020 | Jan | 220 |
2020 | Feb | 230 |
2020 | Mar | 240 |
2020 | Apr | 250 |
2020 | May | 260 |
I need May 2020 sales as March 2020 + April 2020, April 2020 sales as March 2020. March 2020 sales needs to March 2019 to Feb 2020 (Sum of sales)
Hi @Anonymous
take a look at the following solution:
Sum of Sales = SUM('Table'[Sales])
Running Total =
CALCULATE(
[Sum of Sales],
FILTER(
ALLEXCEPT('Table','Table'[Year]),
'Table'[Date] <= MAX('Table'[Date])
)
)
With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)
@Anonymous , id every month logic is changing if need rolling you can do that with date table
2/3
Rolling 3 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-3,MONTH))
Rolling 3 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX(Sales[Sales Date]),-3,MONTH))
Rolling 3 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-3,MONTH))
Rolling 3 till last 1 month = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],Startofmonth(dateadd(Sales[Sales Date],-1,month)),3,MONTH))
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184
Appreciate your Kudos.
Amit, I am using the same logic to create 6 months rolling sums. But for YTD I am failing, I am wondering is there a way to create the same maybe as a calculated column?
Hi, @Anonymous
Could you please tell me whether your problem has been solved?
If yes, you could accept the helpful answer as solution. You also could share your own solution here. For now, there is no content of description in the thread. If you still need help, please share more details to us.
Best Regards,
Community Support Team _ Eason
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 |
---|---|
111 | |
99 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
111 | |
92 | |
84 | |
66 |