Calculate Cumulative values on monthly

[Anonymous]

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! 

[Ashish_Mathur]

Hi,

Share some data and show the expected result.

[Anonymous]

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]

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]

Try it with a variable.