Forum Discussion
Dax Sum values for dynamic date period
Hi team,
I have two tables. Table 1 is DirectQuery and Table 2 is in Import mode.
Table 1:
Date1 | Values |
02/01/2022 01:00:00 AM | 5 |
02/01/2022 03:00:00 AM | 10 |
02/01/2022 05:10:00 PM | 20 |
03/01/2022 10:15:00 PM | 10 |
03/01/2022 10:15:00 AM | 20 |
04/01/2022 10:15:00 PM | 10 |
05/01/2022 10:15:00 PM | 15 |
Table 2:
Shop | Date2 |
ABC | 01/01/2022 01:00:00 AM |
ABC | 01/01/2022 01:00:00 AM |
DDS | 03/01/2022 10:15:00 PM |
DDS | 03/01/2022 10:15:00 PM |
DDS | 03/01/2022 10:15:00 PM |
DDS | 03/01/2022 10:15:00 PM |
Tables have relationship via third table. Table 3 to Table 1 via ID, Table 2 to table 3 via Shop.
Combined Table 1 and Table 2 looks this way:
Date1 | Shop | Values (is calc.column) | Date2 | Calc Amount |
02/01/2022 01:00:00 AM | ABC | 5 | 01/01/2022 01:00:00 AM | 5 |
02/01/2022 03:00:00 AM | ABC | 10 | 01/01/2022 01:00:00 AM | 10 |
02/01/2022 05:10:00 PM | DDS | 20 | 03/01/2022 10:15:00 PM | 0 |
03/01/2022 10:15:00 AM | DDS | 20 | 03/01/2022 10:15:00 PM | 0 |
04/01/2022 10:15:00 PM | DDS | 10 | 03/01/2022 10:15:00 PM | 10 |
05/01/2022 10:15:00 PM | DDS | 15 | 03/01/2022 10:15:00 PM | 15 |
Whenever we have a “Date2” for some shop, the Values until “Date2” (for the specific location) become 0. If Date1 > Date2, the Calc.Amount is the same as the Values
For the Calc.Amount, I’m using this formula suggested by @smpa01 here: https://community.powerbi.com/t5/Desktop/Dax-change-values-based-on-a-date/m-p/2250986#M819078
Measure =
VAR _dt1 =
MAX ( 'Table 1'[Date1] )
VAR _dt2 =
CALCULATE (
MAX ( 'Table 2'[Date2] ),
CROSSFILTER ( 'Table 1'[Shop], 'Table 2'[Shop], BOTH )
)
VAR _amt =
MAX ( 'Table 1'[Amount] )
VAR _comp =
IF ( _dt2 <> BLANK () && _dt1 > _dt2, _amt )
RETURN
_comp
Currently, I need to modify this formula this way:
If Date1 > Date2, return the sum of the values for the period after Date2 group by location. Wanted result:
Date1 | Shop | Values (Calc.column) | Date2 | NEW: Calc Amount |
02/01/2022 01:00:00 AM | ABC | 5 | 01/01/2022 01:00:00 AM | 15 |
02/01/2022 03:00:00 AM | ABC | 10 | 01/01/2022 01:00:00 AM | 15 |
02/01/2022 05:10:00 PM | DDS | 20 | 03/01/2022 10:15:00 PM | 0 |
03/01/2022 10:15:00 AM | DDS | 20 | 03/01/2022 10:15:00 PM | 0 |
04/01/2022 10:15:00 PM | DDS | 10 | 03/01/2022 10:15:00 PM | 25 |
05/01/2022 10:15:00 PM | DDS | 15 | 03/01/2022 10:15:00 PM | 25 |
Thank you.
Thanks for all members who read the post.
I figured it out. For future reference: First, I added a calculated column "DatesComparison" which compares Date1 with Date 2, and if Date1>Date2, returns 1, otherwise 0. After that I added another Calculation "Sum_Values" = CALCULATE(Table2[Calc]Amount],Table2[DatesComparison] <>0)
1 Reply
Thanks for all members who read the post.
I figured it out. For future reference: First, I added a calculated column "DatesComparison" which compares Date1 with Date 2, and if Date1>Date2, returns 1, otherwise 0. After that I added another Calculation "Sum_Values" = CALCULATE(Table2[Calc]Amount],Table2[DatesComparison] <>0)