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,
Need a calculated column for daily values from cumulative values type and category wise. Table is as per below -
Type | Category | Date | Cumulative | Daily |
Domestic | A | 21-04-2020 | 1 | |
Domestic | A | 10-05-2020 | 5 | |
Domestic | A | 11-05-2020 | 9 | |
Domestic | B | 21-04-2020 | 15 | |
Domestic | B | 22-04-2020 | 16 | |
Domestic | B | 29-04-2020 | 32 | |
Domestic | B | 11-05-2020 | 32 | |
Domestic | C | 21-04-2020 | 1 | |
Domestic | C | 22-04-2020 | 1 | |
Domestic | C | 23-04-2020 | 1 | |
Domestic | C | 24-04-2020 | 9 | |
Export | E | 21-04-2020 | 36 | |
Export | E | 22-04-2020 | 36 | |
Export | E | 25-04-2020 | 51 | |
Export | E | 26-04-2020 | 53 | |
Export | F | 27-04-2020 | 53 | |
Export | F | 28-04-2020 | 54 | |
Export | F | 29-04-2020 | 58 | |
Export | F | 30-04-2020 | 61 | |
Export | F | 01-05-2020 | 67 | |
Export | F | 02-05-2020 | 71 | |
Export | F | 03-05-2020 | 78 | |
Export | F | 04-05-2020 | 78 | |
Export | F | 05-05-2020 | 80 | |
Export | F | 06-05-2020 | 80 | |
Export | F | 07-05-2020 | 83 |
Create two calculate columns using dax as below:
Rank = RANKX(FILTER('Table', 'Table'[Type] = EARLIER('Table'[Type]) && 'Table'[Category] = EARLIER('Table'[Category])), 'Table'[Index], , ASC, Dense)
Column =
VAR Current_Rank = 'Table'[Rank]
VAR Current_CumulativeDaily = 'Table'[CumulativeDaily]
RETURN
IF (
Current_Rank = 1,
Current_CumulativeDaily,
Current_CumulativeDaily
- CALCULATE (
MAX ( 'Table'[CumulativeDaily] ),
FILTER (
'Table',
'Table'[Category] = EARLIER ( 'Table'[Category] )
&& 'Table'[Type] = EARLIER ( 'Table'[Type] )
&& 'Table'[Rank] = Current_Rank - 1
)
)
)
Community Support Team _ Jimmy Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @sayali_deshmukh ,
Try this code:
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 |
---|---|
114 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |