Cumulative Total in months filter

DAX DAX Calculations
1

Cumulative values is showing wrong in 2020 and 2019 but 2021 is correct even I am using same measures in all three. Please find snapshot for reference and below mentioned measures.

Capture
Capture815×476 77.5 KB

Amount Paid 2020 = CALCULATE([Amount Paid QAR],Data,‘Calendar Table’[Year]=“2020”) CORRECT

Cumulative Paid 2020 =
CALCULATE([Amount Paid 2020],
FILTER(ALLSELECTED(‘Calendar Table’),
‘Calendar Table’[Date] <= MAX(‘Calendar Table’[Date]))) WRONG

Cumulative Paid 2021 =
CALCULATE([Amount Paid 2021],
FILTER(ALLSELECTED(‘Calendar Table’),
‘Calendar Table’[Date] <= MAX(‘Calendar Table’[Date]))) CORRECT

2

@khurram Do you have a slicer with a selection over a year? If not, then MAX is returning the MAX date, possibly a date of 2021, whereas the Amount Paid measure is for 2020, that’s why you are getting a single number, you are not manipulating the filter context correctly.

You logic needs to be bit more complex.

VAR VisibleMonth = 
    CALCULATE ( 
        MAX ( Dates[Calendar Year Month Number] ),
        Dates[Calendar Year Number] = 2007
    )
VAR Result = 
    CALCULATE ( 
        [Total Sales],
        Dates[Calendar Year Number] = 2007 &&
        Dates[Calendar Year Month Number] <= VisibleMonth,
        REMOVEFILTERS ( Dates )
    ) 
RETURN
    Result

image
image1494×840 80.3 KB

3

Hi @khurram, did the response provided by @AntrikshSharma help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!

4

Hi @khurram, we’ve noticed that no response has been received from you since August 31.

We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

5

@Sharma Thank you, yes I am not using slicer with a selection over a year.
In the same scenario how can I calculate sum of amount paid 2020 from January to October 2020?

6

@khurram The solution is already shared above. Did you check it?

7

Hi @khurram, we’ve noticed that no response has been received from you since Sep 3.

We just want to follow up if the solution provided above helped your question?

In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

8

@Antriksh Can you please elaborate Dates[Calendar Year Month Number] ?
Dates[Calendar Year Number] = 2007 This column reference is clear where I have years.

9

@khurram Calendar Year Month Number is a column which contains 2 columns combined into one, Month Number and Calendar Year Number.

image
image1095×443 38.5 KB

Instead of preparing a filter context with 2 columns, Calendar Year Month Number helps in using only one column. It is the one of most important columns of a Date table in my opinion.

10

Hi @khurram, did the response provided by @AntrikshSharma help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!

11

Hi @khurram, due to inactivity, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box.