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 table with IDs, dates and values per ID-Date combination. I would like to calculate the difference of the values compared to the previous date for the same ID.
For example: ID 1 increased from 01.11.2021 to 02.11.2021 by 5. So the result is 5.
If an ID was not there on the previous date, the value would be null.
For example: ID 4 came in on 03.11.2021 with the value of 10. The diff prev. date is null since there is no previous date with this ID.
How do I calculate this new "Diff prev. date" column using DAX`?
Thanks in advance.
ID | Date | Value | Diff prev. date |
1 | 01.11.2021 | 10 | null |
2 | 01.11.2021 | 10 | null |
3 | 01.11.2021 | 10 | null |
1 | 02.11.2021 | 15 | 5 |
2 | 02.11.2021 | 8 | -2 |
3 | 02.11.2021 | 10 | 0 |
1 | 03.11.2021 | 15 | 0 |
2 | 03.11.2021 | 11 | 3 |
3 | 03.11.2021 | 10 | 0 |
4 | 03.11.2021 | 10 | null |
Solved! Go to Solution.
@TheHans you can use a measure like this
VAR _sum =
SUM ( 'Table 1'[Value] )
VAR _id =
MAX ( 'Table 1'[ID] )
VAR _date =
MAX ( 'Table 1'[Date] )
VAR _immediatelyPrecedingSum =
CALCULATE (
SUM ( 'Table 1'[Value] ),
FILTER (
ALL ( 'Table 1' ),
'Table 1'[Date]
= CALCULATE (
MAX ( 'Table 1'[Date] ),
'Table 1'[Date] < _date,
ALLEXCEPT ( 'Table 1', 'Table 1'[ID] )
)
&& 'Table 1'[ID] = _id
)
)
RETURN
IF ( _immediatelyPrecedingSum = BLANK (), 0, _sum - _immediatelyPrecedingSum )
If you need a calulated column
Column =
VAR _sum =
CALCULATE ( SUM ( tbl[Value] ) )
VAR _immediatelyPreceding =
MAXX (
FILTER (
tbl,
tbl[ID] = EARLIER ( tbl[ID] )
&& tbl[Date] < EARLIER ( tbl[Date] )
),
tbl[Date]
)
VAR _imediatelyPrecedingSum =
SUMX (
FILTER (
tbl,
tbl[ID] = EARLIER ( tbl[ID] )
&& tbl[Date] = _immediatelyPreceding
),
tbl[Value]
)
RETURN
IF ( _imediatelyPrecedingSum = BLANK (), 0, _sum - _imediatelyPrecedingSum )
Diff_CC =
VAR __prev =
MAXX(
TOPN( 1, FILTER( Table1,
Table1[ID] = EARLIER( Table1[ID] )
&& Table1[Date] < EARLIER( Table1[Date] ) ),
Table1[Date]
),
Table1[Value]
)
RETURN
IF( NOT ISBLANK( __prev ), Table1[Value] - __prev )
Diff =
VAR __prev =
MAXX(
TOPN(
1,
CALCULATETABLE(
Table1,
Table1[Date] < MAX( Table1[Date] ),
ALLEXCEPT( Table1, Table1[ID] )
),
Table1[Date]
),
[Total]
)
RETURN
IF( NOT ISBLANK( __prev ), [Total] - __prev )
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
Hi CNENFRNL,
thanks a lot. Appreciate your support.
H
@TheHans you can use a measure like this
VAR _sum =
SUM ( 'Table 1'[Value] )
VAR _id =
MAX ( 'Table 1'[ID] )
VAR _date =
MAX ( 'Table 1'[Date] )
VAR _immediatelyPrecedingSum =
CALCULATE (
SUM ( 'Table 1'[Value] ),
FILTER (
ALL ( 'Table 1' ),
'Table 1'[Date]
= CALCULATE (
MAX ( 'Table 1'[Date] ),
'Table 1'[Date] < _date,
ALLEXCEPT ( 'Table 1', 'Table 1'[ID] )
)
&& 'Table 1'[ID] = _id
)
)
RETURN
IF ( _immediatelyPrecedingSum = BLANK (), 0, _sum - _immediatelyPrecedingSum )
If you need a calulated column
Column =
VAR _sum =
CALCULATE ( SUM ( tbl[Value] ) )
VAR _immediatelyPreceding =
MAXX (
FILTER (
tbl,
tbl[ID] = EARLIER ( tbl[ID] )
&& tbl[Date] < EARLIER ( tbl[Date] )
),
tbl[Date]
)
VAR _imediatelyPrecedingSum =
SUMX (
FILTER (
tbl,
tbl[ID] = EARLIER ( tbl[ID] )
&& tbl[Date] = _immediatelyPreceding
),
tbl[Value]
)
RETURN
IF ( _imediatelyPrecedingSum = BLANK (), 0, _sum - _imediatelyPrecedingSum )
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 |
---|---|
47 | |
24 | |
20 | |
15 | |
13 |
User | Count |
---|---|
55 | |
48 | |
43 | |
19 | |
19 |