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.
Hello all! I've got a data set with cumulative amounts. I'm trying to calculate the non-cumulative monthly totals using DAX . How can I get PowerBI to identify total amount as of today and subtract total amount from last month?
Let me know if you need any more info. Thanks in advance!
Current Data Setup:
MonthName | FiscalMonthInt | Amount (Cumulative) |
OCT | 01 | 67,904,161 |
NOV | 02 | 16,1620,357 |
DEC | 03 | 322,803,193 |
JAN | 04 | 488,696,422 |
FEB | 05 | 714,247,442 |
MAR | 06 | 966,124,818 |
APR | 07 | 1,133,638,748 |
MAY | 08 | 1,407,021,012 |
JUN | 09 | 1,518,0556,75 |
JUL | 10 | 0 |
AUG | 11 | 0 |
SEP | 12 | 0 |
I'd like to create a DAX measure that creates a non-cumulative column.
MonthName | FiscalMonthInt | Amount (Cumulative) | Amount (Non-cumulative) |
OCT | 01 | 67,904,161 | 67,904,161 |
NOV | 02 | 161,620,357 | 93,716,196 |
DEC | 03 | 322,803,193 | 161,182,836 |
JAN | 04 | 488,696,422 | 165,893,229 |
FEB | 05 | 714,247,442 | 225,551,020 |
MAR | 06 | 966,124,818 | 251,877,376 |
APR | 07 | 1,133,638,748 | 167,513,930 |
MAY | 08 | 1,407,021,012 | 273,382,264 |
JUN | 09 | 1,518,0556,75 | 111,034,663 |
JUL | 10 | 0 | 0 |
AUG | 11 | 0 | 0 |
SEP | 12 | 0 | 0 |
Seems simple but I can't figure it out. Thanks all.
Solved! Go to Solution.
Hi,
MyMeasure =
VAR ThisMonthInt =
MIN( 'Table'[FiscalMonthInt] )
VAR PreviousMonthInt = ThisMonthInt - 1
VAR ThisAmount =
MIN( 'Table'[Amount (Cumulative)] )
VAR PreviousAmount =
CALCULATE(
MIN( 'Table'[Amount (Cumulative)] ),
FILTER( ALLSELECTED( 'Table' ), 'Table'[FiscalMonthInt] = PreviousMonthInt )
)
RETURN
IF( ThisAmount = 0, 0, ThisAmount - PreviousAmount )
Regards
Hi,
MyMeasure =
VAR ThisMonthInt =
MIN( 'Table'[FiscalMonthInt] )
VAR PreviousMonthInt = ThisMonthInt - 1
VAR ThisAmount =
MIN( 'Table'[Amount (Cumulative)] )
VAR PreviousAmount =
CALCULATE(
MIN( 'Table'[Amount (Cumulative)] ),
FILTER( ALLSELECTED( 'Table' ), 'Table'[FiscalMonthInt] = PreviousMonthInt )
)
RETURN
IF( ThisAmount = 0, 0, ThisAmount - PreviousAmount )
Regards
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 |
---|---|
46 | |
28 | |
22 | |
12 | |
8 |
User | Count |
---|---|
75 | |
53 | |
46 | |
15 | |
12 |