Running total for missing dates

[bweiland]

Hello, I have data like below , how do I either create a running total column or a dax measure that will create the running total for Jan 2 equal to the Jan 1 values?  There are many 'blank' dates throughout the year not just one.

[Vijay_Chethan · Accepted solution]

Hello bweiland,
i have used a separate date table to include missing dates in sales table and set up relationship between 
date table and sales table:

Then i used the following measure to get desired running total, i also observed running total and sales are grouped by GL account

Running Total =

CALCULATE(

    SUM('Sales_Table'[sales]),

    FILTER(

        ALLSELECTED('Date_table'[Date]),

        ISONORAFTER('Date_table'[Date], MAX('Date_table'[Date]), DESC)

    )

)

if this helps, please mark as solution

[danextian · Accepted solution]

Hi @bweiland 

You can't assign a value to a row that doesn't exist in your data. If January 2 isn't present in your table, no value can be shown for it. To handle this, use a dedicated calendar table with a continuous range of dates, and make sure it's marked as a date table in your model. Set and use date tables in Power BI Desktop

Running Total = 
CALCULATE (
    SUM ( FactData[Value] ),
    FILTER (
        ALL ( CalendarTable[Date] ),
        CalendarTable[Date] <= MAX ( CalendarTable[Date] )
    )
)

Please see the attached pbix.

[bweiland]

Yes,this works but the below also gets the exact same results so I'm interested in what  FILTER ( ALL are required in your query?

Cumulative Total =

CALCULATE(

    SUM('Datatable'[Amount]),

    DATESBETWEEN(DateTable[Date], MINX(ALL(DateTable), DateTable[Date]), MAX(DateTable[Date]))

[Vijay_Chethan · Accepted solution]

Hello bweiland,
i have used a separate date table to include missing dates in sales table and set up relationship between 
date table and sales table:

Then i used the following measure to get desired running total, i also observed running total and sales are grouped by GL account

Running Total =

CALCULATE(

    SUM('Sales_Table'[sales]),

    FILTER(

        ALLSELECTED('Date_table'[Date]),

        ISONORAFTER('Date_table'[Date], MAX('Date_table'[Date]), DESC)

    )

)

if this helps, please mark as solution

[bweiland]

Yes, this works but the below gets the same result so I'm interested on what the FILTER(ALLSELECTED and ISONORAFTER functions are doing?

Cumulative Total =

CALCULATE(

    SUM(Data[Amount]),

    DATESBETWEEN(Data[Date], MINX(ALL(Dates),Dates[Column1]), MAX(Dates[Column1]))

[pankajnamekar25]

Hello @bweiland 

Use this measure

Running Total =

CALCULATE(

    SUM('SalesData'[Sales]),

    FILTER(

        ALL('SalesData'),

        'SalesData'[Date] <= MAX('SalesData'[Date])

            && 'SalesData'[GL Account] = MAX('SalesData'[GL Account])

    )

)

Thanks,
 Pankaj Namekar | LinkedIn

If this solution helps, please accept it and give a kudos (Like), it would be greatly appreciated.

[bweiland]

This though does not does not the Jan 2 date.  Doesn't the query need to reference a data table like the below query?

Cumulative Total =

CALCULATE(

    SUM(Data[Amount]),

    DATESBETWEEN(Data[Date], MINX(ALL(Dates),Dates[Column1]), MAX(Dates[Column1]))