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"m trying to do a table where I can calculate my Year to date Sales vs the same period for the previous year.
Using this measure, and selecting all my dates, I am able to see a side by side of current month vs previous month of 2017 vs 2016
Previous Year Sales = CALCULATE(SUM('Closing'[Sales]),SAMEPERIODLASTYEAR(DATESMTD('Closing[Date])))
However, when I select only 2017 dates and deselect 2016, all my 2016 "previous year" goes away. I try to add a "All" statement to my "Previous Year Sales" so the date does not have an affect on it.
Previous Year Sales= CALCULATE(SUM('Closing'[Sales]),SAMEPERIODLASTYEAR(DATESMTD('Closing'[Date])),all(Year_Month))
If I do that, it seems to fix the problem and my "previousYear Sales" will always be visible. However, my grand total on my previous sale is not adding up.
Basicaly, I want to create a measure where when all dates are selected, it will give me a current vs prior year comparison. But at the same time, if I just select 1 month, 2 month, or 3 month, it will still be able to calculate for those 3 months.
I've attached the *.png file and you will see that on the 2nd column, my grand total is adding up. However, on the 3rd column, it is not adding but only using the most recent number.
First, I recommend using a calendar table. When you are creating measures based on time intelligence, best practice is to have a calendar table built.
This blog by Rob Collie will give you what you need.
To summarize:
[YTD Sales] = CALCULATE ( SUM ( Closing[Sales] ), DATESYTD ( Calendar[Date] ) )
and
[Previous YTD Sales] = CALCULATE ( SUM ( Closing[Sales] ), DATEADD ( DATESYTD ( Calendar[Date] ), -1, YEAR ) )
Now, put Calendar[Month] on your table, and these 2 measures. Throw Calendar[Year] on a slicer, and you're good to go.
thanks. it actually did not work for me. with the code provided, now both of my total sales and previous years sales do not add on the "row".
Also, same issue with the slicer. if I have all my dates selected on the slicer, it gives me the side by side of current versus previous. but if I pick, for example december 2017, my 2016 december goes away.
I am now using a calendar table.
Hi @semidevil,
It will be help if you share some sample data or pbix file to test.
Regards,
Xiaoxin Sheng
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 | |
94 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |