Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Calculate Cumulative values on monthly

Hi,

 

I have a table to calculate Cumulative value total on monthly for ecah year. I gave Visual level filter for 1 year (Find in below screenshot).

Datatable has calender Date relationship with Date column in data table below.

Expect value should be like: 
                                      Jan - 2,667

                                      Feb - 5,334

                                      Mar - 8,001

                                      April - 10,668 .
                                        ........

 

I don't know how below measure is calculating this value. I am geeting wrong value.

Please let me know if i am misisng anything.

Thanks in advance! 

Need Help

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Try something like this:

    Measure = 
var __DateToConsider = MAX(DateTable[Date])
return 
CALCULATE(
    SUM(Financial[Financial Target],
    DateTable[Date] <= __DateToConsider
)

    First, you find the max date within the filter context and store it in a variable. For example, in a row of your table visual, the max date would be the last date of that month.

     

    Then you use the stored value to compare with each date in the DateTable. This overrides the original filter context with the new filter context (less than or equal to last date of original filter context).

     

    Hope this helps,

    Nathan

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      It's not working as expected. 

       

      I notice some wierd thing -

      1.  I worte a measure for particluar value, it is working fine( without slicer).  

      Measure  =
      CALCULATE (
      [Sum of financial],
      FILTER (
      ALL ( Financial ),
      Financial[Month!] <= MAX ( Financial[Month!] )
      && Financial[Queue Name] = "OCOE-CRM-Task Resolution"
      )
      )

      Right output : 

      Jan - 2,667

      Feb - 5,334

      Mar - 8,001

      April - 10,668 .....................

       

      2. I wrote same measure with storerd sclier selection value. (Slicer selection = SELECTEDVALUE(Financial[Queue Name]), and this measure i am in below measure as slicer slection value. 

       

      Measure 1  =
      CALCULATE (
      [Sum of financial],
      FILTER (
      ALL ( Financial ),
      Financial[Month!] <= MAX ( Financial[Month!] )
      && Financial[Queue Name] = [Slicer selection]
      )
      )
      Wrong output : calculating total of all values. 
       
      I am confused here, both measures are same. how they are caluclating values? if i specified in mesure it is working, if  I am using sclier it is not working. 
      Help need
      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Try it with a variable.