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.
Im on a project to get rid of Tableau.
I am having some issues with reporting on a value and the value in the same period last year.
Im aware of the SAMEPERIODLASTYEAR function, my issue is that when there is a store that did not have data for the whole preiod this year, it only shows the data for the same period last year for the days that had a value, NOT the whole period?
Attached is a screenshot showing the values for 01-Aug-2017 to 31-Aug-2017 as well as next to it 01-Aug-2018 to 31-Aug-2018
and then an image of the calculation with the sameperiodlastyear calc included.
It shows that it works as long as the site was there and had sales for the whole period.
It ignores if the site is no longer around (No sales in the Current Period), but i want it included.
I can provide my pbix (Not company confidential info, all values altered) if it will help someone.
Solved! Go to Solution.
SAMEPERIODLASTYEAR did not work, PARALLELPERIOD worked perfectly when set to MONTH - 12
This may cause more of a problem then a help but I have noticed that sometimes if you can get your data to show 0 instead of blank formulas have a way of behving differently.
New Sales = If(SUM(Sales)=0,"0",SUM(Sales))
or
New Sales = If(SUM(Sales)=BLANK(),"0",SUM(Sales))
depending on how the data is working
It would force data where there is none. Just an idea but I have not tested this.
Nah no good, the issue is that for the new time period there isnt data thats zero, there is just NO data.
E.G. the data looks like
01-Aug-2018 | 75
02-Aug-2018 | 150
03-Aug-2018 | 65
04-Aug-2018 | 48
END
But if i set the range to 2017 there is data for the whole month last year, but the powerbi table is only calculating it for the 01 to the 04 august in the above example
The saddest thing is, i can make EXACTLY what i need in a Stored Procedure in SQL, but then it would need to be run every time for every different month range.
SAMEPERIODLASTYEAR did not work, PARALLELPERIOD worked perfectly when set to MONTH - 12
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 |
---|---|
106 | |
94 | |
77 | |
65 | |
53 |
User | Count |
---|---|
145 | |
105 | |
104 | |
90 | |
63 |