DAX Help needed - Same Period Last Year

[PhoenixBird66]

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:

Sales_SamePeriodLastYear = CALCULATE(SalesInvoiceLines[Sales], SAMEPERIODLASTYEAR('Date'[Date]))

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?

[v-lili6-msft]

hi  @PhoenixBird66 

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

[PhoenixBird66]

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.

[PhoenixBird66]

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)...

[v-lili6-msft]

hi  @PhoenixBird66 

If possible, could you please share your sample pbix file for us have a test? that will be a great help.

Regards,

Lin

[amitchandak]

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

[Greg_Deckler]

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...