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 a graph whch looks at sales per month and compares it to the same period last year:
The DAX i used for that measure was:
The problem is that when it looks at the same period last year it counts the whole of the month, but obviously if I refresh the data on the 15th of the month, it is only half way through the month so it shows a bit decrease on the same period last year.
Any idea how I can better show this data without it being misleading (showing a loss because its comparing half a month with a full month last period)? Or perhaps I can change the measure slightly so it looks at the actual date rather than the month?
For your case, just add a conditional in the formula as below:
New Sales_SamePeriodLastYear = CALCULATE(SalesInvoiceLines[Sales], SAMEPERIODLASTYEAR('Date'[Date]),
FILTER(ALL('Date'[Date]),DATEADD('Date'[Date],1,YEAR)<=CALCULATE(MAX(SalesInvoiceLines[Date]),ALLSELECTED(SalesInvoiceLines))))
Regards,
Lin
Thanks for all the suggestions guys but sadly I'm still getting the sales for the whole of March 2019, when what I want is the sales from 1st March 2019 up to 24th March 2019 - this date last year.
I'm stuck and just can't think how to do this.
Here is a picture of the problem.
Last year, March's sales included all dates in March (1st March 2019-31st March 2019), but this year we're not quite at the end of of the month so the column "SalesThis Year" only contains sales data from 1st March 2020 to 23rd March 2020.
I want the Same Period Last Year column to only show March sales between 1st March 2019 and 23rd March 2019 (instead of the full month)...
If possible, could you please share your sample pbix file for us have a test? that will be a great help.
Regards,
Lin
You can use datesytd, totalytd
YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"12/31"))
This Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD((ENDOFYEAR('Date'[Date])),"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
Last YTD complete Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
like
Last YTD Sales =
var _max = maxx(Sales, Sales[sales date])
var _maxly = date(year(_max)-1,month(_max),day(_max))
CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"),Sales[sales date]<=_maxly )
or
See if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...
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 | |
93 | |
82 | |
70 | |
64 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |