topic Re: cumulative by year till today in DAX Commands and Tips

cumulative by year till today

pedroccamaraDBI — Wed, 20 Apr 2022 18:56:37 GMT

Hi guys
I have this measure that shows me the cumulative values.

CALCULATE(

[Sales],

FILTER(

ALLSELECTED(Dates),

Dates[Date] <= MAX( Dates[Date] )))

In this report, i also have a filter to show me the years i want to filter this value.
My question is : if i filter by year 2022, i just want to see values till today().
Can you help me?
Thank you all in advance

Re: cumulative by year till today

SpartaBI — Wed, 20 Apr 2022 19:40:03 GMT

pedroccamaraDBI
Add a column to your Date table like:
"Past/Today/Future" =
SWITCH(
TRUE(),
'Date'[Date] < TODAY(), "Past",
'Date'[Date] = TODAY(), "Today",
"Future"
)

Then add this column to your report/page/visual filter pane (depnding on what you want it to influence) and choose there just "Past" and "Today".
This will show you values up to today in 2022 and will not affect the previous years of course

tamerj1 in what you shown it will work for 2022 but not for the previous years, where it will always show the data until the last date of the year regardless the last date in the visual

Re: cumulative by year till today

tamerj1 — Wed, 20 Apr 2022 19:24:41 GMT

Hi pedroccamaraDBI

just modify

CALCULATE(

[Sales],

FILTER(

ALLSELECTED(Dates),

Dates[Date] <= TODAY( )))

Re: cumulative by year till today

pedroccamaraDBI — Thu, 21 Apr 2022 08:46:13 GMT

Hello SpartaBI
Thanks for your answer. I did a similar suggested thing. I add a column to my date tabe like this one :

IsTodayandB4 = IF (Dates[Date] <= TODAY(), 1, 0) and then i add it to filter my visual.
First issue is solved but not the other:
I want to be able to see all sales up to the year selected, like this

in this case it should show the values from year before and it's not.
Maybe i will have to change the filter in the measure?
Thanks in advance SpartaBI

Re: cumulative by year till today

SpartaBI — Thu, 21 Apr 2022 08:57:57 GMT

Wait, that wasn't clear in your first post :))
You want for each year to see the cumulative value from the beginning of the previous year?
So, let's say in March 2021 you want to see the sum of Jan 2020 - March 2021 and not Jan 2021 - March 2021?

Re: cumulative by year till today

pedroccamaraDBI — Thu, 21 Apr 2022 09:04:40 GMT

Yes SpartaBI but not only from previous year but all previsous years, cose i know there's data from 2003. If i select 2021 I should see all years before and the same if i select 2022 and so on

Re: cumulative by year till today

SpartaBI — Thu, 21 Apr 2022 09:10:53 GMT

oh... classic running total 🙂 why didn't you say so in the first place haha
just switch the allselected with all 🙂

CALCULATE(

[Sales],

FILTER(

ALL(Dates),

Dates[Date] <= MAX( Dates[Date] )
)
)

Re: cumulative by year till today

pedroccamaraDBI — Thu, 21 Apr 2022 09:23:42 GMT

I'm so sorry, SpartaBI cose sometimes we write with our head and not with our fingers 😀 (or is it the other way around?)
One last question:
Now it's showing, i believe, the proper values....but only the cumulative for this year which is selected. Shouldn't it show also all months from year before?

Re: cumulative by year till today

SpartaBI — Thu, 21 Apr 2022 09:31:22 GMT

The months shown in the Axis (Rows/Columns/Bars etc) are determined by your filter selection in the Slicer or anything else that affect the visual.
Their actual values are determined by the measure we are writing so we are actually changing the filter context for the result iteself but the months for which this will shown are still subject to the filters outside the visual. If you will remove the filter from the slicer you will see all the years and the of course the right value of the measure for each point

Re: cumulative by year till today

pedroccamaraDBI — Thu, 21 Apr 2022 09:40:39 GMT

Thank you so much for all your help SpartaBI
Have a nice day 😀