Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Cumulative totals

Hi All, 

 

is there a way i can get a cumulative total based on slicer selection. For example, if September-21 is selected on slicer, then i want my total sales to be sum of  July + Aug + Sept

 

Thanks

 

General Comment
Interesting Links
Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Anonymous ,

     

    The calendar table can be created like

    Table 2 = CALENDAR(MIN('Table'[Date]),MAX('Table'[Date]))

    There's no relationship between tables.

     

    The following two measures can meet the requirements.

    total = 
var _sel=SELECTEDVALUE('Table 2'[Date])
return
CALCULATE(SUM('Table'[Amount]),FILTER('Table',YEAR([Date])=YEAR(_sel)&&[Date]<=_sel))
    total2 = TOTALYTD(SUM('Table'[Amount]),'Table'[Date])

    The date field in the slicer is from the calendar table.

     

     

    Best Regards,

    Stephen Tao

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

4 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    Anonymous , Try a measure like this with help from the date table

     

    Rolling 3 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-3,MONTH))

     

    To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi Amit, 

       

      Thanks! 

       

      This is a sample table: 

      DepartmentCost TypeDateAmount
      SalesForecast01/06/20215
      SalesBudget01/06/20215
      MarketingForecast01/06/20214
      MarketingBudget01/06/20215
      SalesForecast 01/05/20216
      SalesBudget01/05/20217

       

      I do not have a dim_calendar table as i'm only bringing in one table in my Power BI model. 

       

      When i bring my date slicer from my table, if i select 01/06/2021 and i want to find total Budget for the Sales department , i want my total budget to be 7 + 5 = 12 (sales for 01/06/2021 + 01/05/2021) So i essentially want a YTD view but i dont have a dim date table so can i still use time intelligence?

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Hi Anonymous ,

         

        The calendar table can be created like

        Table 2 = CALENDAR(MIN('Table'[Date]),MAX('Table'[Date]))

        There's no relationship between tables.

         

        The following two measures can meet the requirements.

        total = 
var _sel=SELECTEDVALUE('Table 2'[Date])
return
CALCULATE(SUM('Table'[Amount]),FILTER('Table',YEAR([Date])=YEAR(_sel)&&[Date]<=_sel))
        total2 = TOTALYTD(SUM('Table'[Amount]),'Table'[Date])

        The date field in the slicer is from the calendar table.

         

         

        Best Regards,

        Stephen Tao

         

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.