topic Re: Running total for different ids for the months not having any data in DAX Commands and Tips

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

Anonymous — Tue, 28 Sep 2021 14:06:52 GMT

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.

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

amitchandak — Tue, 28 Sep 2021 14:18:45 GMT

Anonymous , 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])))

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

Jihwan_Kim — Tue, 28 Sep 2021 14:19:34 GMT

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

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

Anonymous — Wed, 29 Sep 2021 13:52:17 GMT

Thank you. It worked. Appericiate your help.