I am creating a report that shows costs on a project by cost group. The date filter will be for a month (example the November filter will be 11/1/24 to 11/30/24). I need to create 3 measures. Using November as the guide:
Measure 1 - Sum all previous costs before 11/1/24
Measure 2 - Sum all costs between 11/1/2024 and 11/30/2024
Measure 3 - Total costs to date
I apologize but I can’t disclose the cost data that is confidential to the client. The name of the file with the amounts to be summed is
‘tjobcostdetails’[Amount].
Date file for date:
‘Calendar’[Date]
I looked at other posts in the Forum and the results are not correct. I looked at the Sep 2021 Post Max(date[Date]) as a guide but it did not calculate. I am using a Calendar file as part of the data model. Any help is appreciated!
While we wait for other members to share their insights, we’ve taken the liberty of using “Explain Simply,” one of the tools available within Data Mentor.
Feel free to checkout more of our Data Mentor features as you work on your report. These tools are designed to help with tasks like the one you’re working on, and they might accelerate your learning curve.
Hi @jimkelliher - Please share in which calculation you are getting the error. Also based on above I assume in your model you have a Calendar table and Fact table.
Here is what is happening. I have a date filter to identify the month we want to show as current costs, in this case 11/1/2024 to 11/30/2024. My measure creates numbers for the previous costs before 11/1/2024 as $0, because of my filter. How do I write a Measure for previous total cost that will only use the Date filter to determine the total cumulative costs before 11/1/24 (earliest date on the filter) and bypass the filter that is calling for only costs between 11/1/2024 and 11/30/2024? When I use the formula from the AI Chat in this chain, it does work…but only if I do not have a date filter included. In addition, we want the staff to just put in date ranges as needed using a Slicer so they do not have to manually change dates in the measure.
I hope this makes sense. I would also need a measure that calcuates the current costs using the dates in the slicer/filter. In this case that is costs between 11/1/24 and 11/30/24. Does that help?
Hi @jimkelliher - Can try a measure like below. This will give cumulative total for all dates before the earliest date on Filter. Can replace columns with your model ones.
Before Total = var mindate = min(Dates[Date])
return
CALCULATE([Total Sales],Dates[Date] <= mindate)
For current costs bases on slicer, you don’t need anything specific I guess. if you have a measure say Sum(cost), Power BI should show values based on slicer.
