topic DAX help - calculate MTD, YTD and Full Year in DAX Commands and Tips

DAX help - calculate MTD, YTD and Full Year

Anonymous — Thu, 17 Feb 2022 02:11:03 GMT

I did not managed to create my table to show MTD, YTD and Full Year for comparative year(s). Pls advise where did I go wrong. File url: https://1drv.ms/x/s!Aj1jIHqeNbLXkyDkyO0tSLgHeFi9?e=a6Dg4N

Re: DAX help - calculate MTD, YTD and Full Year

amitchandak — Thu, 17 Feb 2022 03:51:23 GMT

Anonymous , That excel, what measures you have tried

example with date table

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

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))

Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5bd4
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 :radacad sqlbi My Video Series Appreciate your Kudos.

Re: DAX help - calculate MTD, YTD and Full Year

Anonymous — Thu, 17 Feb 2022 06:15:38 GMT

Hi, amitchandak. Recommended solution does not work.

I have separate datasets for 2020 and 2021 which capture month by month sales volume, net sales and gross margin by product. These 2 datasets are linked to an auto generated date table "Calendar". The data table is linked to a separate table "Month" listing the 12 months (used as slicer).

Measures created in the sales datasets as follows:

2020 dataset:

MTD Sales = CALCULATE(SUM(2020[Amount]),DATESMTD('Calendar'[Date]))

YTD Sales = CALCULATE(SUM(2020[Amount]),DATESYTD('Calendar'[Date]))

Total Year Sales = CALCULATE(SUM(2020[Amount]),ALL('Month'[Month]))

Similar measures are created in the 2021 sales dataset.

Outcome: pivot table shows 2021 MTD, YTD and Total Year data correctly, but no data reflected in 2020 columns.

Pls advise solution.

Re: DAX help - calculate MTD, YTD and Full Year

amitchandak — Thu, 17 Feb 2022 13:06:04 GMT

Anonymous , if you table 2020 has dates of 2020, then you have to create last year/year behind measure for YTD/MTD etc

as same date table is applied to both tables, so current year is 2021

I have give example in my last post

Refer if needed

Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs