Forum Discussion
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.
**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
- amitchandak
Super User
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
- AnonymousNot applicable
- AnonymousNot applicable
amitchandak can you please help me on this.
- AnonymousNot applicable
Can anyone please guide me on this.
- AnonymousNot applicable
amitchandak can you please help me on this.
I am really struggling to get this done.
- AnonymousNot 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 _resultBest Regards,
Gao
Community Support TeamIf 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