topic Re: To Date trends based on global report filter in DAX Commands and Tips

To Date trends based on global report filter

arnavn961 — Tue, 04 Feb 2025 04:58:39 GMT

Hey guys,

I'm designing a dashboard in Power BI for a mining company. I have several tabs within that dashboard, one for a project summary, one for cost performance, one for schedule performance, etc. I will be building in a project selection screen at the start of the dashboard, which allows the user to filter the visuals in each tab to represent a particular project and data from or up to a particular reporting month for that project. Id like to create a measure that :

Filters a visual with months on its X-axis to show data only up to the reporting month chosen in the project selection screen, e.g. if my date slicer is set to March 2025, I'd like the monthly cost values for each month up to March 2025 to be visible in the visual for that page. Note that the month selected would not necessarily be the latest month within the data.

Re: To Date trends based on global report filter

bhanu_gautam — Tue, 04 Feb 2025 05:29:40 GMT

arnavn961 First create a date table

DateTable = CALENDAR(MIN(YourData[Date]), MAX(YourData[Date]))

Create the Measure:

DAX
FilteredCost =
CALCULATE(
SUM(YourData[Cost]),
FILTER(
ALL(YourData),
YourData[Date] <= MAX(DateTable[Date])
)
)

Add a slicer to your report.
Use the Date column from the DateTable in the slicer.

Add a visual (e.g., line chart) to your report.
Use the Date column from the DateTable on the X-axis.
Use the FilteredCost measure for the values.

Re: To Date trends based on global report filter

arnavn961 — Tue, 04 Feb 2025 06:20:40 GMT

That would work if the latest month in my data was the selected month in the slicer, but the max value in my date column could go to a couple of years later than the selected date as well.

Re: To Date trends based on global report filter

divyed — Tue, 04 Feb 2025 07:15:02 GMT

Hello arnavn961 ,

Here are the steps you can take :

1. Create a Reporting Month Slicer (Project Selection Screen)

Make sure to sync slicers if you have slicers on different pages or apply this as page level filter if needed.

you might have a Date Table that includes columns like Date, Month, Year, Month-Year, etc.

Make sure the reporting month is used in a slicer, and you have the Month-Year or Monthcolumn from the Date Table available for users to select the reporting month.

2. Create a Measure to Filter Data Based on Reporting Month

This measure will check the selected reporting month from the slicer and only display data up to that

month.

Example : Suppose you want to calculate a cost measure, such as Total Cost

Total Cost Up to Reporting Month =
VAR ReportingMonth = MAX('ReportingMonth'[Month]) -- Get the selected reporting month
RETURN
CALCULATE(
SUM('CostData'[Cost]), -- Sum of cost data
'Date'[Month] <= ReportingMonth -- Filter data for months less than or equal to the reporting month
)

3 Handle Slicers and Filters Effectively :

If you're using multiple slicers (e.g., project, cost type, etc.), be sure that your measures are also accounting for those selections. Hence I would recommend to create measure accordingly.

I hope this help.

Did I answer your query ? Mark this as solution if this helps, kudos are appreciated.

Warm Regards,

Neeraj

Re: To Date trends based on global report filter

Anonymous — Wed, 19 Feb 2025 05:48:32 GMT

Hi, arnavn961

May I ask if you have gotten this issue resolved? If it is solved, please share your solution and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster.

If it is not resolved, I hope you will provide the full .pbix file via OneDrive or SharePoint. Please be careful to remove all sensitive information and we will do our best to provide ideas for your issue.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Fen Ling,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.