Running total for different ids for the months not having any data

[Milan6687]

Hi folks,

I need to create the running total for different ids for the range of date selected via slicer.

For e.g. I have TableA with below values:

Date                  Id        Name       Revenue

01/01/2021       01       Laptop      500

02/02/2021       01        Laptop      300

01/01/2021        02       Chair         200

02/02/2021        02       Chair          300

If there is no data for any month it should return previous running total as shown below. For March and April there is no data but I previous running total is expected. Tried creating one date table and have left outer join but not worked.

Date               Id          Name        RunningRevenue

jan 21    01         Laptop               500

feb 21     01        Laptop              800

mar 21    01        Laptop              800

apr 21     01        Laptop              800

jan 21     02         Chair                 200

feb 21     02         Chair                 500

mar 21    02         Chair                 500

apr 21     02         Chair                 500

Waiting for some guidance.

Thanks in Advance.

[Jihwan_Kim · Accepted solution]

Hi,

I suggest having a calendar table, and data model structure like below.

All measures are in the sample pbix file (link down below).

Link to the sample pbix file 

[Milan6687]

Thank you. It worked. Appericiate your help.

[amitchandak]

@Milan6687 , Try with date table joined with date of you table, use month from date

calculate(sum(Table[revenue]), filter(allselected('Date'), 'Date'[Date] <= max('Date'[Date])))

or with date and ID table

calculate(sum(Table[revenue]), filter(allselected('Date'), 'Date'[Date] <= max('Date'[Date])), filter(allselected(ID), ID[Id] =max(ID[id])))