cancel
Showing results for
Did you mean:
Frequent Visitor

## Running Total with Condition

Hi everyone,

I'm creating a measure to calculate a running total with different conditions. Specifically, when Condition 1 is blank, Total should be 0, then Running Total is calculated with the period (1,3,6,12) ( See expected result).

Here is the measure I created to obtain the maxtrix below:

Test_Summarize =
VAR newTablle = FILTER(ADDCOLUMNS(SUMMARIZE(Table1,Table1[Operator],"Condition1",[Condition1]),"FH",[Total]),[Condition1]<>BLANK())
VAR result = SUMX(newTablle,[FH])
RETURN
result

Test_Running_Total = CALCULATE([Test_Summarize],DATESBETWEEN (
'Calendar'[Date],
NEXTDAY ( LASTDATE( 'Calendar'[Date] ) ),
-1
* IF (
HASONEVALUE ( 'Period'[Period] ),
MAX ( 'Period'[Period] ),
12
),
MONTH
),
MAX( ( 'Calendar'[Date] )
)
))

Expected Result:

Thank you!

1 ACCEPTED SOLUTION
Super User
Test_Running_Total = CALCULATE(sumx(values(calendar[yearmonth]),if([condition1],[total])),DATESBETWEEN (
'Calendar'[Date],
NEXTDAY ( LASTDATE( 'Calendar'[Date] ) ),
-1
* IF (
HASONEVALUE ( 'Period'[Period] ),
MAX ( 'Period'[Period] ),
12
),
MONTH
),
MAX( ( 'Calendar'[Date] )
)
))
Super User
Test_Running_Total = CALCULATE(sumx(values(calendar[yearmonth]),if([condition1],[total])),DATESBETWEEN (
'Calendar'[Date],
NEXTDAY ( LASTDATE( 'Calendar'[Date] ) ),
-1
* IF (
HASONEVALUE ( 'Period'[Period] ),
MAX ( 'Period'[Period] ),
12
),
MONTH
),
MAX( ( 'Calendar'[Date] )
)
))

Announcements