Running total with Blank values

[MohanVanks]

Hello All,

I am trying to calcualte running total with the columns coming from three different tables.

First Table - Calender - Using Date Column

2nd Table - Clouser Table - using Matching Variable Column, MIS Column

3rd table - VOLUME - using VOLUME column

Data Modelling

I have created a measure using below formula which is using matching variable from CLOUSER table, VOLUME from VOLUME table.

_IPTV = 
DIVIDE(COUNT('GM Clouser'[Matching_Variable]),sum([Volume]),0)*1000+0

the sample data looks as below where year, month from Calendar table, MIS, Matchin variable from CLOUSER table, VOLUME from VOLUME table.

Please see the below link where you can find the csv data.

data.csv

**Please note that the data is coming from three different table here**

I have written the dax code to get the running total as below.

Running Total IPTV = 
 VAR MaxDate = max('CalendarTable'[Date])
 RETURN
CALCULATE (SUMX(CalendarTable,DIVIDE(COUNT('GM Clouser'[Matching_Variable]),sum([Volume]),0)*1000+0), 
    FILTER( ALLSELECTED( 'CalendarTable'), 'CalendarTable'[Date] <= MaxDate),
    FILTER(ALLSELECTED('GM Clouser'),'GM Clouser'[MIS] = MAX ( 'GM Clouser'[MIS] ))
)

I values are coming right but for the blank values of matching variable where IPTV values are 0, it is returning blank in running total 

the running total values should get carryforward if it is blank.

Can anyone please guide me to correct the same.

Thanks,

Mohan V.

[v-cgao-msft]

Hi @MohanVanks ,

Please try.

Iptv total = SUMX(ALLSELECTED('CalendarTable'[Year],'CalendarTable'[Month]),[_IPTV])

Running Total IPTV = 
VAR _lastvisibledate =
    MAX ( 'CalendarTable'[Date] )
VAR _firstvisibledate =
    MIN ( 'CalendarTable'[Date] )
VAR _lastdatewithiptv =
    CALCULATE ( MAX ( 'GM Clouser'[Date] ), REMOVEFILTERS () )
VAR _result =
    IF (
        _firstvisibledate <= _lastdatewithiptv,
        CALCULATE (
            [Iptv total],
            'CalendarTable'[Date] <= _lastvisibledate,
            REMOVEFILTERS ( 'CalendarTable' )
        )
    )
RETURN
    _result

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[MohanVanks]

@amitchandak can you please help me on this.

I am really struggling to get this done.

[MohanVanks]

Can anyone please guide me on this.

[amitchandak]

@MohanVanks , I do not think a second filter is needed

Running Total IPTV =
VAR MaxDate = max('CalendarTable'[Date])
RETURN
CALCULATE (SUMX(CalendarTable,DIVIDE(COUNT('GM Clouser'[Matching_Variable]),sum([Volume]),0)*1000+0),
FILTER( ALLSELECTED( 'CalendarTable'), 'CalendarTable'[Date] <= MaxDate)
)

You can also explore Window

Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc

[MohanVanks]

@amitchandak thanks for the reply.

If i remove it, then the running total is not coming right.

[MohanVanks]

@amitchandak can you please help me on this.