Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Ad
@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.
@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
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
@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.
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:
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
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.
@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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
41 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
44 | |
32 | |
30 | |
18 | |
17 |