Forum Discussion

bweiland's avatar
bweiland
Regular Visitor
1 year ago
Solved

Running total for missing dates

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.

 

 

Need Help
  • Vijay_Chethan's avatar
    Vijay_Chethan
    1 year ago

    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's avatar
    danextian
    1 year ago

    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.

     

     

6 Replies

  • pankajnamekar25's avatar
    pankajnamekar25
    Super User
    1 year ago

    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's avatar
      bweiland
      Regular Visitor
      1 year ago

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

       

  • Vijay_Chethan's avatar
    Vijay_Chethan
    Resolver III
    1 year ago

    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's avatar
      bweiland
      Regular Visitor
      1 year ago

      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]))
  • danextian's avatar
    danextian
    Super User
    1 year ago

    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's avatar
      bweiland
      Regular Visitor
      1 year ago

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