Running Total as continous line in Combo Chart

[gselvag]

Hi folks,
I have got challenge resolve a running total as a continous line, and that cut/break after last value (date).

DataTable:
The datatable have different forecasts (ref ReportPeriod) that need to be evaluated as seperate measure.

Measures:

Since there's multiple forecasts dataset, we made a measure to filter by MIN(DATA[ReportPeriod]). This measure works fine.

GM (BAC) --- period values: THIS IS OK!

GM (BAC) = 
VAR Report_BAC = 'Measure'[Report BAC]   //MIN(DATA[ReportPeriod])
RETURN
CALCULATE (
    SUMX(DATA; 
        DATA[Value] * [CurrFactor]);
        DATA[ReportPeriod]=Report_BAC
    )

GM (BAC) Total --- value running total: NOT WORK AS EXPECTED!

GM (BAC) Total = 
    CALCULATE(
        SUMX( DATA; [GM (BAC)]);
        FILTER( ALL('Calendar'[Date]); 'Calendar'[Date]<= MAX('Calendar'[Date]))
        )

The GM (BAC) Total should show a running end total of 850 000,- but include both forecasts, thus existing filter's removed. How to include filter from DATA table to show correct value (ref. RED) and blank value after 2018/Aug (ref. BLUE).

Appreciate any feedback and guidance for a correct running total measure. 

[v-jiascu-msft]

Hi @gselvag,

Could you please mark the proper answers as solutions?

Best Regards,

Dale

[davehus]

Hi,

This video might solve your issue https://youtu.be/YwEtrvJQVC8.

D

[gselvag]

GM (BAC) Total.. = 
VAR FirstDateRange = [FirstDateRange]
VAR LastDateRange =  [LastDateRange]
VAR GM_Total = TOTALYTD([GM (BAC)]; 'Calendar'[Date])
RETURN
IF ( MAX('Calendar'[Date])>= FirstDateRange; 
    IF( MIN('Calendar'[Date])<= LastDateRange; GM_Total; BLANK());BLANK())

Give this result:

This doesn't resolve my issue. It return blank in 2018/Jul. Try to fix by change to:

GM (BAC) Total.. = 
VAR FirstDateRange = [FirstDateRange]
VAR LastDateRange =  [LastDateRange]
VAR GM_Total = TOTALYTD([GM (BAC)]; 'Calendar'[Date])
RETURN
IF ( MAX('Calendar'[Date])>= FirstDateRange; GM_Total; BLANK())

But give this result, a running total to the end of the year and not 2018/aug.



We don't understand why this occur, and can't combine two condition (Values between Min/Max data date range). Please advise, thanks for any assistance here.  

[v-jiascu-msft · Accepted solution]

Hi @gselvag,

Try this formula, please. 

GM (BAC) Total =
VAR maxReportPeriod =
    CALCULATE ( MAX ( Data[ReportDate] ), ALL ( 'Calendar' ) )
RETURN
    IF (
        MIN ( 'Calendar'[Date] ) > maxReportPeriod,
        BLANK (),
        CALCULATE (
            [GM (BAC)],
            FILTER (
                ALL ( 'Calendar'[Date] ),
                'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
            )
        )
    )

Best Regards,

Dale