Need help with MTD feature

[msoileau]

Ok everyone.  I'm pulling my hair out.  Just would like to get an idea of this months payments to date compared to last month's payements to date but doesn't seem to be working out for me.

I've downloaded a Date table and entered relationships in already.  

My amount MTD formula is as follows:

Amount MTD = TOTALMTD(sum('proforma pivot v3'[Payment]), 'Calendar'[Date])

It seems that any MTD formula I use doesn't give me a cumaltive snapshot in the month to date compared with the same time on another month but instead a total of payments so far in the month.  

What I want is: payments so far on april 18 2020 compared with payments so far on april 18 2019.

[v-jayw-msft]

Hi @msoileau ,

Check the Pbix as attached.

I created two measures for current year payment and last year payment and a slicer for you to filter year and month.

If i misunderstand your meaning, please show some sample data and expected out put to us.

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak · Accepted solution]

@msoileau ,

You can try time intelligence and date calendar

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

last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
last year MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-12,MONTH))))
last QTR same Month (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,Qtr))))
MTD (Year End) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFYEAR('Date'[Date])))
MTD (Last Year End) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFYEAR(dateadd('Date'[Date],-12,MONTH),"8/31")))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

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

Can't really tell what you want but maybe:

Amount MTD = TOTALMTD(sum('proforma pivot v3'[Payment]), 'Calendar'[Date])

Amount MTD Last Year = Calculate([Amount MTD],SAMEPERIODLASTYEAR('Calendar'[Date]))

Difference = [Amount MTD] - [Amount MTD Last Year]