Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Running total with Blank values

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.

 

6 Replies

  • Anonymous , 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

    • Anonymous's avatar
      Anonymous
      Not applicable

      amitchandak thanks for the reply.

       

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

       

       

  • Anonymous's avatar
    Anonymous
    Not applicable

    Can anyone please guide me on this.

  • Anonymous's avatar
    Anonymous
    Not applicable

    amitchandak can you please help me on this.

    I am really struggling to get this done.

  • Anonymous's avatar
    Anonymous
    Not applicable

    Hi Anonymous ,

     

    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