Forum Discussion

tamdo95's avatar
tamdo95
Frequent Visitor
5 years ago
Solved

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],
DATEADD (
NEXTDAY ( LASTDATE( 'Calendar'[Date] ) ),
-1
* IF (
HASONEVALUE ( 'Period'[Period] ),
MAX ( 'Period'[Period] ),
12
),
MONTH
),
MAX( ( 'Calendar'[Date] )
)
))

Expected Result:

Thank you!

 

  • wdx223_Daniel's avatar
    wdx223_Daniel
    5 years ago
    Test_Running_Total = CALCULATE(sumx(values(calendar[yearmonth]),if([condition1],[total])),DATESBETWEEN (
    'Calendar'[Date],
    DATEADD (
    NEXTDAY ( LASTDATE( 'Calendar'[Date] ) ),
    -1
    * IF (
    HASONEVALUE ( 'Period'[Period] ),
    MAX ( 'Period'[Period] ),
    12
    ),
    MONTH
    ),
    MAX( ( 'Calendar'[Date] )
    )
    ))

1 Reply

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Community Champion
    5 years ago
    Test_Running_Total = CALCULATE(sumx(values(calendar[yearmonth]),if([condition1],[total])),DATESBETWEEN (
    'Calendar'[Date],
    DATEADD (
    NEXTDAY ( LASTDATE( 'Calendar'[Date] ) ),
    -1
    * IF (
    HASONEVALUE ( 'Period'[Period] ),
    MAX ( 'Period'[Period] ),
    12
    ),
    MONTH
    ),
    MAX( ( 'Calendar'[Date] )
    )
    ))