Column for "last Month in period"

[Danfountain]

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):

CALCULATE(MAX(FACT_BulkUploads[DateEOM]),ALLSELECTED (FACT_BulkUploads))

But for some reason its taking the entire dataset and not what has been filtered in via the slicer.  So the slicer is based on a calendar table with a relationship on the "Date" field.  If i chose 12 Months its fine because it comes back with 31/1/2024 and inline with the main filter it runs from Feb 23 - Jan 24.  However if i switch to calander months it then runs between Jan 23 - Dec 23 BUT the above calculate is still coming back with 31/1/2024.

What am i doing wrong?  Basically I need maximum date from the current filtered dataset (filtered with the slicer on the calendar table) so i know what the "last month" is in the dataset.

Help! 🙂

Many thanks

Dan

[123abc]

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:

• We first calculate the maximum end-of-month date (MaxDateInFilteredContext) considering all selected dates from the calendar table, as indicated by ALLSELECTED('Calendar').
• Then, we use this calculated maximum date to filter the calendar table and return the maximum date corresponding to the end of the last month in the filtered context.

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.

[Danfountain]

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:

TestCol =

VAR MaxDateInFilteredContext =

CALCULATE (

MAX ( FACT_BulkUploads[DateEOM] ),

ALLSELECTED ( 'CalendarTable' ) // Assuming 'Calendar' is your calendar table

)

RETURN

CALCULATE (

MAX ( 'CalendarTable'[Date] ),

'CalendarTable'[Date] = MaxDateInFilteredContext

)

But its coming back saying that a circular dependancy was detected.....

I cannot work out what would be cicular about it.

Dan

[Danfountain]

Please hold off responding.  I might have found my mistake.

[Danfountain]

Hi,

So this is my measure at the moment:

IsNewestMonth_TEST_3 =

VAR MaxDateInFilteredContext =

CALCULATE (

MAX ( FACT_ECS_BulkUploads[DateEOM] ),

ALLSELECTED ( 'CalendarTable' ) // Assuming 'Calendar' is your calendar table

)

RETURN

CALCULATE (

MAX ( 'CalendarTable'[Date] ),

'CalendarTable'[Date] = MaxDateInFilteredContext

)

If i put this into a table - what its doing is returning the last day of the month for each record.  So a November record is coming back as last day in Nov, and a December record is coming back as last day in December.

So just to clarify.....

I would expect that if i selected 12 Calendar Months - at the moment that would be Jan23-Dec23.  So i would expect that to come back with Dec23 on every record.  On Feb 1st - this would then become Feb23-Jan23 so it would then become Jan23 on every record.

I cannot see whats going wrong here 🙂

[Danfountain]

Got it

InScopeMonth = VAR MaxInScope = CALCULATE(MAX(CalendarTable[EOM]),ALLEXCEPT(CalendarTable,CalendarTable[SOM]))

Return IF(MaxInScope = max(FACT_BulkUploads[DateEOM]),"Y","N")

Thank you for sending me down the right path.

[123abc]

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.

[123abc · Accepted 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.