Forum Discussion

PRAVEENSC2023's avatar
PRAVEENSC2023
Frequent Visitor
2 years ago

Calculating Rolling total when date table is not available

Hi All,

 

This is somewhat getting over my head. I am calculating rolling total based on the year=2011 and Month wise. We do not have date table in the database.

 

to give more clarity i have used 

 

Total Due FROM SalesOrderheader table in AdventureWorks database

Year is coming from OrderDate column in adventureWorks database

Measure i used is  as below.please let me know how to do it

olling Total = CALCULATE([TOTAL SALES]
                ,FILTER(ALL('Sales SalesOrderHeader'[OrderDate],'Sales SalesOrderHeader'[OrderDate]<=MAX('Sales SalesOrderHeader'[OrderDate]))))
               

 

YEAR ORDERMONTH            TotalDue
2011          May                      567020.9498
2011          June                      507096.469
2011          July                       2292182.8828
2011          August                  2800576.1723
2011         September            554791.6082
2011         October                 5156269.5291
2011         November             815313.0152
2011          December            1462448.8986

Tips and Tricks

1 Reply

  • some_bih's avatar
    some_bih
    Icon for Community Champion rankCommunity Champion
    2 years ago

    Hi PRAVEENSC2023 

    it is hard to spot your issue / figure out exact request based on inputs from you. Still, try version 2 below

    Rolling Total v2 = 
     
    VAR __max_date=MAX('Sales SalesOrderHeader'[OrderDate])
     
    RETURN
    CALCULATE([TOTAL SALES],
     
    FILTER(ALL('Sales SalesOrderHeader'[OrderDate],
    'Sales SalesOrderHeader'[OrderDate]<=__max_date)
    )
    )