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(...
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

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.

newbie9292's avatar
newbie9292
Helper II
5 years ago

Hello PaulDBrown ,


Thanks for your reply. Yes the columns are in date format now and the slicers are coming from the date table but still the issue remains.

  • aj1973's avatar
    aj1973
    Community Champion
    5 years ago

    Hi newbie9292 

    Can I see how you formated the Date column in AE_2020?

    Also please Copy paste the visual into another page and show us.

    • newbie9292's avatar
      newbie9292
      Helper II
      5 years ago

      Hello aj1973 ,

      This how my formatted date column looks like:

       

      This is the visual :

      When I select 2020 in the slicer, the cummulated value is shown correctly : 

      But when I select 2020 and 2021 in the slicer, it messes up the cummulated data for 2020 only : 

       

       

      • aj1973's avatar
        aj1973
        Community Champion
        5 years ago

        Well that's normal because the column Month Name is cumulating the sum for January 2020 and January 2021 (it's an example), meaning your formula is not calculating it right upon the filters.

         

        Maybe use Month Name_Year should solve the issue