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 have some reports that I'm trying to calculate the LY period of Sales data. The Sales Data is always behind by a couple days in the SQL table.
I created a calendar date of all sales data ship date records. I use this date for my slicer.
Calendar = CALENDAR(Min(SalesData[Ship Date]),MAX(SalesData[Ship Date]))
LY Yards = CALCULATE(SUM(SalesData[Yards]),SAMEPERIODLASTYEAR('Calendar'[Date]))
Everything works great except when I use the last date from the file 1-1-2018 to 1-17-2018 then the LY Yards is some erroneous number way off.
If I change the Calendar function to
Calendar = CALENDAR(Min(SalesData[Ship Date]),(today()))
Then 1-1-2018 to 1-17-2018 works fine.
It is not a data issue. I don't want to use the Today because we want to show the slicer of what dates truly exist in the data.
Tomorrow 1-17-2018 will work and 1-18-2018 won't.
This is happening on all of my Last Year calculations.
I would appreciate any advice.
Any chance you can share sample PBIX? This sounds strange and I don't think it is solveable without sample data. The dates returned should be the same as the dates returned from:
DATEADD(dates, -1, year)
So, perhaps try substituting that or create a new table and see what is being returned in terms of dates when using your last date in the file.
So where should I substitute DATEADD(dates, -1, year) in the formula?
LY Yards = CALCULATE(SUM(SalesData[Yards]),SAMEPERIODLASTYEAR('Calendar'[Date]))
Hi,
In the SAMEPERIODLASTYEAR() portion.
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 |
---|---|
96 | |
95 | |
83 | |
70 | |
66 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |