topic Re: Help with Dax - Day on Day difference, with MTD and YTD in DAX Commands and Tips

Help with Dax - Day on Day difference, with MTD and YTD

Anonymous — Mon, 20 Jul 2020 12:31:12 GMT

Hi, can you please assist with my DAX issue?

I have this data - see table below:

The Day on Day difference is taking the current day sales and subtracting the previous day sales.

This was achieve using the dax below:

Day on Day difference =
VAR currentDate = MAX('Date'[Date])
VAR priorDate = CALCULATE(MAX('Date'[Date]), ALL('Date'[Date]), 'Date'[Date] < currentDate)
VAR priorDateSales = CALCULATE(SUMX('Sales','Sales'[DailySales), 'Date'[Date] = priorDate)
VAR CurrentDaySales = CALCULATE(SUMX('Sales','Sales'[DailySales), 'Date'[Date] = currentDate)
VAR daydiff = CurrentDaySales-priorDateSales
RETURN
daydiff

The problem I have is I need the YTD and MTD totals to be used in a Card Visual, but i only get the yesterday value (for 17th July) as you see in the table above.

Please how do I calculate the YTD and MTD totals for the Day on Day difference?

Thanks.

Re: Help with Dax - Day on Day difference, with MTD and YTD

Greg_Deckler — Mon, 20 Jul 2020 12:38:16 GMT

Anonymous - So, typically with YTD and MTD you use time intelligence functions with a separate date table. https://docs.microsoft.com/en-us/dax/time-intelligence-functions-dax

Also, 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-TITHW/m-p/434008

Re: Help with Dax - Day on Day difference, with MTD and YTD

Anonymous — Mon, 20 Jul 2020 13:02:16 GMT

Thanks Greg_Deckler

Unfortunately this did not help - when i used the DATEYTD and the date table, i only got the same result as the Yesterday measure:

I am hoping to see the total of 36,130 (all days), instead of 0 which is the yesterday value for 17th July

Re: Help with Dax - Day on Day difference, with MTD and YTD

Greg_Deckler — Mon, 20 Jul 2020 13:08:22 GMT

Anonymous - Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

Re: Help with Dax - Day on Day difference, with MTD and YTD

Anonymous — Mon, 20 Jul 2020 13:34:07 GMT

Greg_Deckler

I have added my sample data as text below.

Original data - Date and DailySales:

Date	DailySales
01-Jul-20	-35,300
02-Jul-20	-38,150
03-Jul-20	-35,800
06-Jul-20	-23,650
07-Jul-20	-30,900
08-Jul-20	-19,750
09-Jul-20	-28,700
10-Jul-20	-25,850
13-Jul-20	-17,610
14-Jul-20	-5,960
15-Jul-20	-2,220
16-Jul-20	-2,220
17-Jul-20	-2,220
Total

1st task - to calculate the day on day difference in the days, so Today sales - yesterday sales.

I could not use the DATEADD or PREVIOUSDAY functions as the dates have gaps - no sales on weekends.

So to achive that, I used the dax fomular below to create a data on day difference column:

Date	DailySales	Day on Day difference
01-Jul-20	-35,300	3,050
02-Jul-20	-38,150	-2,850
03-Jul-20	-35,800	2,350
06-Jul-20	-23,650	12,150
07-Jul-20	-30,900	-7,250
08-Jul-20	-19,750	11,150
09-Jul-20	-28,700	-8,950
10-Jul-20	-25,850	2,850
13-Jul-20	-17,610	8,240
14-Jul-20	-5,960	11,650
15-Jul-20	-2,220	3,740
16-Jul-20	-2,220	0
17-Jul-20	-2,220	0
Total		0

This works fine for Yesterday's Day on Day difference, I now want the MTD and YTD totals for the Day on Day column.

When i use CALCULATE(Day on Day difference, DATEMTD('Date'[Date])), i still get the same 0 total, i expect to see 31,130

Date	DailySales	Day on Day difference	Day on Day with DATEYTD	Expected Results
01-Jul-20	-35,300	3,050	3,050	3,050
02-Jul-20	-38,150	-2,850	-2,850	-2,850
03-Jul-20	-35,800	2,350	2,350	2,350
06-Jul-20	-23,650	12,150	12,150	12,150
07-Jul-20	-30,900	-7,250	-7,250	-7,250
08-Jul-20	-19,750	11,150	11,150	11,150
09-Jul-20	-28,700	-8,950	-8,950	-8,950
10-Jul-20	-25,850	2,850	2,850	2,850
13-Jul-20	-17,610	8,240	8,240	8,240
14-Jul-20	-5,960	11,650	11,650	11,650
15-Jul-20	-2,220	3,740	3,740	3,740
16-Jul-20	-2,220	0	0	0
17-Jul-20	-2,220	0	0	0
Total		0	0	31,130

I hope this makes my issue clearer.

Re: Help with Dax - Day on Day difference, with MTD and YTD

amitchandak — Mon, 20 Jul 2020 14:23:01 GMT

Anonymous , rfer these I tried answer some those questions

https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a

https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e

https://medium.com/@amitchandak.1978/power-bi-day-intelligence-questions-time-intelligence-5-5-5c3243d1f9

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/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184

Appreciate your Kudos.

Re: Help with Dax - Day on Day difference, with MTD and YTD

amitchandak — Mon, 20 Jul 2020 14:45:36 GMT

Anonymous , do want to stop YTD a day before

YTD Sales till yesterday = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"), Date[Date] <=Max(Date[Date])-1 )

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))

Day level formula is in the blog, I already shared