cumulative by year till today

[pedroccamaraDBI]

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

[tamerj1]

Hi @pedroccamaraDBI 

just modify

CALCULATE(

    [Sales],

    FILTER(

        ALLSELECTED(Dates),

        Dates[Date] <= TODAY( )))

[SpartaBI]

@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

[pedroccamaraDBI]

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 

[SpartaBI]

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?

[pedroccamaraDBI]

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

[SpartaBI · Accepted solution]

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] )
     )
)

[pedroccamaraDBI]

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? 

[SpartaBI]

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

[pedroccamaraDBI]

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