DAX formula Day to Day for Year -1

[LeroyPaul]

Hi,

I need help to get the right formula for my dashboard

As you can see on my screenshot, I want to see the MTD from last year.

I managed to do it but the total in my table is not the right one.

The total is the total of the month, while I would like the total of the month until today's date minus 1 year

To obtain the measurement Invoiced Sum YEAR-1 I used the following formula :

Invoiced Sum YEAR-1 = CALCULATE([Invoiced_Sum],SAMEPERIODLASTYEAR('Calendar'[Date]))

To obtain the measurement Invoiced MTD YEAR -1 I used the following formula :

Invoiced MTD YEAR -1 = IF([Invoiced Sum YEAR-1] <> BLANK(), CALCULATE('Main'[Invoiced Sum YEAR-1],DATESMTD('Calendar'[Date])),BLANK())

To obtain the measurement Invoiced MTD YEAR -1 per DAY  I used the following formula :

Invoiced MTD YEAR -1 per DAY = IF( NOT ISBLANK([Invoiced_Sum]), CALCULATE('Main'[Invoiced Sum YEAR-1],DATESMTD('Calendar'[Date])),BLANK())

Thanks a lot to those who can help me
Paul

[LeroyPaul]

I probably misspoke

I will illustrate with an example

I would like to calculate last year's MTD for the same day

For example, today is the 22/03/2022,

I would like to calculate the MTD for the month of March 2021 until 22/03/2021 (Today's date minus 1 year)

This is what I managed to do in the screenshot but the total should be 22,521 instead of having the total of the whole month 27,206

[amitchandak · Accepted solution]

@LeroyPaul , When use month it start getting month end date, so we need force

LYMTD QTY forced=
var _max = date(year(today())-1,month(today()),day(today()))
return
if(max('Date'[Date])<=_max, CALCULATE(Sum('order'[Qty]),DATESMTD(dateadd('Date'[Date],-1,year)),'Date'[Date]<=_max), blank())
//OR
//CALCULATE(Sum('order'[Qty]),DATESMTD(dateadd('Date'[Date],-1,year)),'Date'[Date]<=_max)
//TOTALMTD(Sum('order'[Qty]),dateadd('Date'[Date],-1,year),'Date'[Date]<=_max)

or

LYMTD =
var _min = eomonth(today(),-12)+1
var _max1 = today()
var _max = date(year(_max1)-1,month(_max1), day(_max1))
return
CALCULATE(sum('Table'[Qty]), FILTER(ALL('Table'),'Table'[Date] >= _min && 'Table'[Date] <=_max ) )

[LeroyPaul]

Thanks a lot @amitchandak , the following formula was good 😊 :
OR
CALCULATE(Sum('order'[Qty]),DATESMTD(dateadd('Date'[Date],-1,year)),'Date'[Date]<=_max)

TOTALMTD(Sum('order'[Qty]),dateadd('Date'[Date],-1,year),'Date'[Date]<=_max)

[amitchandak]

@LeroyPaul , for last year same month MTD you should try measure like

last year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH)))

last month MTD

last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))