Forum Discussion

newbie9292's avatar
newbie9292
Helper II
5 years ago
Solved

Help with DAX for cummulative data

I want to get the running total or cummulative total in the "KUM_LY_AE" column like in "KUM_AE" column but only getting summarized data. This the dax query I am using : 

KUM_LY_AE = CALCULATE (SUM(AE_2020[AuftrEing]),FILTER (ALLSELECTED(Date_dim[Date]),Date_dim[Date] <= MAX (Date_dim[Date])))

I have 2020 data and current year data in different tables and connected these tables with a calendar table.

Please help.

Need Help
  • v-deddai1-msft's avatar
    v-deddai1-msft
    5 years ago

    Hi newbie9292 ,

     

    Please use the following measure:

     

    KUM_LY_AE = CALCULATE (SUM(AE_2020[AuftrEing]),FILTER (ALLSELECTED(Date_dim[Date]),Date_dim[Date] <= MAX (Date_dim[Date])&&MONTH(Date_dim[Date])<=MONTH(MAX (Date_dim[Date]))))

     

    It works well based on my test data. 

     

    You can also refer to my test pbix.

     

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

     

    Best Regards,

    Dedmon Dai

18 Replies

  • aj1973's avatar
    aj1973
    Community Champion
    5 years ago

    Hi newbie9292 

    This column here, from which Table is it added to the visual?

    And how the Dim_Date and AE_2020 tables are connected?

    • newbie9292's avatar
      newbie9292
      Helper II
      5 years ago

      Hello Amine,

       

      Thanks for your reply.

      This is how my model looks like : 

       

      This is how the tables are connected : 

       

      • PaulDBrown's avatar
        PaulDBrown
        Community Champion
        5 years ago

        You need to set the data type for you date column in the fact tables to "date". It looks like they are set to type "whole number".

        also make sure the fields in your visuals and slicer all come from the date table.

  • v-deddai1-msft's avatar
    v-deddai1-msft
    Community Support
    5 years ago

    Hi newbie9292 ,

     

    Please use the following measure:

     

    KUM_LY_AE = CALCULATE (SUM(AE_2020[AuftrEing]),FILTER (ALLSELECTED(Date_dim[Date]),Date_dim[Date] <= MAX (Date_dim[Date])&&MONTH(Date_dim[Date])<=MONTH(MAX (Date_dim[Date]))))

     

    It works well based on my test data. 

     

    You can also refer to my test pbix.

     

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

     

    Best Regards,

    Dedmon Dai