Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hey all,
I basically have a report that uses a "range" slicer to pick up the last X months or X calendar months.
On one of the visuals I am trying to only show the details for the last month in that set. So I have a calculated column which contains the EOMonth for the date of the record. Then i was planning do to something like this (and then follow up with seeing if the two were the same):
Solved! Go to Solution.
Thank you for the clarification. It seems like you want to retrieve the last day of the month for each record based on the filtered context set by your slicer on the CalendarTable.
Your measure seems to be on the right track. It calculates the maximum end-of-month date in the filtered context and then returns that maximum date for each record in the CalendarTable.
The behavior you're experiencing where each record returns the last day of its corresponding month is expected, as the measure calculates the maximum end-of-month date for the entire filtered context and then returns that date for each record.
If you want to ensure that each record in your table displays the last day of the month based on the filtered context set by your slicer, the measure you provided should achieve that.
If you're seeing different results, it might be due to the way the slicer interacts with your data model or the specifics of the data and relationships between your tables. Double-checking your data model and relationships, as well as the slicer settings, could help troubleshoot any discrepancies you're encountering. Additionally, verifying that the DateEOM column in your FACT_ECS_BulkUploads table and the Date column in your CalendarTable are correctly formatted as dates could also be important.
To achieve your desired outcome of finding the maximum end-of-month date from the currently filtered dataset, you need to ensure that the CALCULATE function considers the filter context set by the slicer on your calendar table.
Here's how you can modify your DAX expression to achieve this:
LastMonthEndDate =
VAR MaxDateInFilteredContext =
CALCULATE (
MAX ( FACT_BulkUploads[DateEOM] ),
ALLSELECTED ( 'Calendar' ) // Assuming 'Calendar' is your calendar table
)
RETURN
CALCULATE (
MAX ( 'Calendar'[Date] ),
'Calendar'[Date] = MaxDateInFilteredContext
)
In this expression:
Make sure to replace 'Calendar' with the actual name of your calendar table in your data model.
This modified expression should give you the maximum end-of-month date for the last month in your filtered dataset based on the slicer selection.
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.
Firstly - thank you so much for a quick response.
I had tried something similar at one point (I have lost track of how many things i have tried!)
So i now have:
Please hold off responding. I might have found my mistake.
Hi,
So this is my measure at the moment:
Got it
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.
Thank you for the clarification. It seems like you want to retrieve the last day of the month for each record based on the filtered context set by your slicer on the CalendarTable.
Your measure seems to be on the right track. It calculates the maximum end-of-month date in the filtered context and then returns that maximum date for each record in the CalendarTable.
The behavior you're experiencing where each record returns the last day of its corresponding month is expected, as the measure calculates the maximum end-of-month date for the entire filtered context and then returns that date for each record.
If you want to ensure that each record in your table displays the last day of the month based on the filtered context set by your slicer, the measure you provided should achieve that.
If you're seeing different results, it might be due to the way the slicer interacts with your data model or the specifics of the data and relationships between your tables. Double-checking your data model and relationships, as well as the slicer settings, could help troubleshoot any discrepancies you're encountering. Additionally, verifying that the DateEOM column in your FACT_ECS_BulkUploads table and the Date column in your CalendarTable are correctly formatted as dates could also be important.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
33 | |
16 | |
13 | |
10 | |
8 |
User | Count |
---|---|
59 | |
20 | |
12 | |
11 | |
10 |