Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
pedroccamaraDBI
Post Partisan
Post Partisan

cumulative by year till today

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
1 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] <= MAXDates[Date] )
     )
)

View solution in original post

9 REPLIES 9
tamerj1
Super User
Super User

Hi @pedroccamaraDBI 

just modify

CALCULATE(
    [Sales],
    FILTER(
        ALLSELECTED(Dates),
        Dates[Date] <= TODAY( )))
 
SpartaBI
Community Champion
Community Champion

@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

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
apagar.JPG
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 

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?

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

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] <= MAXDates[Date] )
     )
)

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? 
apagar2.JPG

 

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

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

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.