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,
I have a Calendar Table, and a Sales Table as below:
Date Week 10/2/2018 40 10/3/2018 40 10/4/2018 40 10/5/2018 40 10/6/2018 40 10/7/2018 40 10/8/2018 41 10/9/2018 41 10/10/2018 41 10/11/2018 41 10/12/2018 41 10/13/2018 41 10/14/2018 41 10/15/2018 42 10/16/2018 42
Sales Date 10 10/7/2018 12 10/7/2018 14 10/8/2018 15 10/9/2018 17 10/11/2018
I created a measure to calculate the previous day Sales:
=CALCULATE(sum(Sales[Sales]), FILTER(ALL('Calendar'), 'Calendar'[Date] = MAX('Calendar'[Date])+1))
how can I create a Sum of Previous Day Sales by week?
I want the result to be like:
Week Previous Day Sales Sum of Sales 40 36 22 41 32 46 Grand Total 68 68
Excel file: https://1drv.ms/x/s!Aps8poidQa5zk45I9gA6JQR-pMfzoA
Thanks,
Solved! Go to Solution.
Sure, if you're using a version of DAX without TREATAS (such as PowerPivot) then you can use INTERSECT instead:
Previous Day Sales = VAR DateFilter = SELECTCOLUMNS ( VALUES ( 'Calendar'[Date] ), "Date-1", 'Calendar'[Date] - 1 ) RETURN CALCULATE ( SUM ( Sales[Sales] ), ALL ( 'Calendar' ), INTERSECT ( ALL ( 'Calendar'[Date] ), DateFilter ) )
This page is a good reference:
https://www.sqlbi.com/articles/propagate-filters-using-treatas-in-dax/
Oh, if you're version of DAX doesn't have INTERSECT either (e.g. Excel 2013) then you would have to use an unwieldy expression using CONTAINS:
= CALCULATE ( SUM ( Sales[Sales] ), ALL ( 'Calendar' ), FILTER ( ALL ( 'Calendar'[Date] ), CONTAINS ( SUMMARIZE ( ADDCOLUMNS ( VALUES ( 'Calendar'[Date] ), "Date-1", 'Calendar'[Date] - 1 ), [Date-1] ), [Date-1], 'Calendar'[Date] ) ) )
Regards,
Owen 🙂
Hi @Iamnvt
From your description, you just want to shift the date values from the current filter context earlier by 1 day.
In that case, I would recommend this:
= CALCULATE ( SUM ( Sales[Sales] ), DATEADD ( 'Calendar'[Date], -1, DAY ) )
Your sample measure & outputs were adding 1 to the max date which seems strange - you should be subtracting if anything.
I would expect output looking like this:
Week | Previous Day Sales | Sum of Sales |
40 | 22 | |
41 | 68 | 46 |
Grand Total | 68 | 68 |
Thanks,
it seems to work well!.
could you help to get me to understand the difference between the 2 measures?
=CALCULATE(sum(Sales[Sales]), FILTER(ALL('Calendar'), 'Calendar'[Date] = MAX('Calendar'[Date])+1))
= CALCULATE ( SUM ( Sales[Sales] ), DATEADD ( 'Calendar'[Date], -1, DAY ) )
That's good!
I think it's best looked at visually.
By the way, I'm assuming you meant to subtract 1 from the date in your original measure (not add 1).
Assuming you are filtering by week (not by day), the Calendar filters look like this:
In other words, DATEADD takes each date visible in the initial filter context and subtracts one day from each in this case, i.e. shifts the whole range one day earlier.
However the MAX(...) expression only gives you a single date (one less than the max date) for each 'cell' of your visual.
Regards,
Owen
hi Owen,
this is an excellent explanation.
I have a variation of this problem: instead of showing date, I have a day sequence of number: 1,2...,10.
Dateadd function cannot apply to the number. How could I address this issue?
DateWeek
1 | 40 |
2 | 40 |
3 | 40 |
4 | 40 |
5 | 40 |
6 | 40 |
7 | 41 |
8 | 41 |
9 | 41 |
10 | 41 |
11 | 41 |
12 | 41 |
13 | 41 |
14 | 42 |
15 | 42 |
Thank you very much
If your "dates" are represented by an integer key instead, you could use a pattern like this.
I'm assuming 'Calendar'[Date] column is now an integer key.
Previous Day Sales = VAR DateFilter = TREATAS ( SELECTCOLUMNS ( VALUES ( 'Calendar'[Date] ), "Date-1", 'Calendar'[Date] - 1 ), 'Calendar'[Date] ) RETURN CALCULATE ( SUM ( Sales[Sales] ), ALL ( 'Calendar' ), DateFilter )
This pattern should also work if 'Calendar'[Date] is actually a date.
It basically subtracts 1 from every visible Date value and applies that as a filter on the Date column, which I assume is similar to what DATEADD does behind the scenes.
Regards,
Owen 🙂
hi Owen,
Do you have other ways of working on Excel? Treatas function is working on Power BI Desktop only.
Thanks again,
Sure, if you're using a version of DAX without TREATAS (such as PowerPivot) then you can use INTERSECT instead:
Previous Day Sales = VAR DateFilter = SELECTCOLUMNS ( VALUES ( 'Calendar'[Date] ), "Date-1", 'Calendar'[Date] - 1 ) RETURN CALCULATE ( SUM ( Sales[Sales] ), ALL ( 'Calendar' ), INTERSECT ( ALL ( 'Calendar'[Date] ), DateFilter ) )
This page is a good reference:
https://www.sqlbi.com/articles/propagate-filters-using-treatas-in-dax/
Oh, if you're version of DAX doesn't have INTERSECT either (e.g. Excel 2013) then you would have to use an unwieldy expression using CONTAINS:
= CALCULATE ( SUM ( Sales[Sales] ), ALL ( 'Calendar' ), FILTER ( ALL ( 'Calendar'[Date] ), CONTAINS ( SUMMARIZE ( ADDCOLUMNS ( VALUES ( 'Calendar'[Date] ), "Date-1", 'Calendar'[Date] - 1 ), [Date-1] ), [Date-1], 'Calendar'[Date] ) ) )
Regards,
Owen 🙂
Excellent! Thanks a lot.
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 |
---|---|
113 | |
103 | |
77 | |
66 | |
63 |
User | Count |
---|---|
142 | |
105 | |
102 | |
81 | |
68 |