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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
LisaBureau
Frequent Visitor

Filtering in DAX formula

I have a calendar showing the last day of each month from Jan 2015 to Dec 2027 ('Calendar'[Dates])

 

I have an actual cost table with dates ('Actual Costs'[Date]) - also last day of each month -

1. from Feb 2021 Measure = CALCULATE(FIRSTDATE('Cost Graphs'[Date]),('Cost Graphs'[Cost Actual]>0))

2. to July 2023 - Measure = CALCULATE(LASTDATE('Cost Graphs'[Date]),('Cost Graphs'[Cost Actual]>0))

 

I want a graph with 'Calendar'[Dates] as my x-axis giving the cumulative of 'Cost Graphs'[Value] - Measure = sum('Cost Graphs'[Cost Actual]) - but only between the First Date and Last Date - otherwise it must be blank.

 

So I am trying to follow online, but am confused about which "date" goes where.  This is where I am at but it is soooo not working:

 

Cumulative Actual = IF(SELECTEDVALUE('Calendar'[Dates])>[Cumulative Last Actual Date],BLANK(),IF(SELECTEDVALUE('Calendar'[Dates])<[Cumulative First Actual Date],BLANK(),
CALCULATE('Cost Graphs'[Cost Actual Total],FILTER(ALLSELECTED('Calendar'[Dates],'Cost Graphs'[Date]<=[Cumulative Last Actual Date])))))
 
Please help.
2 REPLIES 2
Greg_Deckler
Super User
Super User

@LisaBureau First, I would clean up the logic using SWITCH(TRUE(), ...) instead of nexted if statements. Second, have a look at this article on running totals and the associated video. Third, posting sample data and expected output would be extremely helpful.

Better Running Total - Microsoft Fabric Community


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

I need it to graph only between my First and Last date - I think it is the "filter" portion I'm not getting.  How do I filter my Calendar dates to only be between my First and Last on my x-axis, is really my question?

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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