Dax formula to show value for the endo of month or max date.

[gmcalister]

Hi,

I am trying to write a formula that shows a value for either the end of the month or the max date based on a filter.

The formula shows the value for the end of month ("EOM") but it will not work for the max date ("MAX").

My formula reads as follows:

Current Stock Value =

VAR MaxDate =

MAX('Date Table'[Date])

VAR EOM =

ENDOFMONTH('Date Table'[Date])

RETURN

SWITCH(

TRUE(),

'Inventory Management: Tableau Data Source'[Calendar Day] = MaxDate, "MAX",

'Inventory Management: Tableau Data Source'[Calendar Day] = EOM,"EOM",

BLANK()

)

Thanks!

[v-yanjiang-msft · Accepted solution]

Hi @gmcalister ,

According to your description, I guess the Current Stock Value is a calculated column in your sample, as in measure, if you want to refer to a column, you need to add MAX/MIN/SUM before the column.

A calculated column can't recognize the selected date in a slicer, in other words, calculated column can't dynamically change by a slicer, it only change when the report refresh.

Additionally, if the two tables have relationship, based on the context, MAX('Date Table'[Date]) wil not return the max value in the slicer, replace it with MAXX ( ALLSELECTED('Date Table'),'Date Table'[Date] ).

Here's my solution, create a measure.

Current Stock Value =
VAR MaxDate =
    MAXX ( ALLSELECTED ( 'Date Table' ), 'Date Table'[Date] )
VAR EOM =
    ENDOFMONTH ( 'Date Table'[Date] )
RETURN
    SWITCH (
        TRUE (),
        MAX ( 'Inventory Management: Tableau Data Source'[Calendar Day] ) = MaxDate, "MAX",
        MAX ( 'Inventory Management: Tableau Data Source'[Calendar Day] ) = EOM
            && EOM <> BLANK (), "EOM",
        BLANK ()
    )

Get the correct result.

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please considerAccept it as the solution to help the other members find it more quickly.

[johnt75]

Try changing the measure to just return MaxDate, that might shed some light on what is going on.

[gmcalister]

Hi Johnt75,

It returns a blank. It is almost as if it doesn't recognise that the 2 dates are the same.

[johnt75]

I presume that you have a one-to-many relationship from the Date table to your inventory table, in which case I don't think the date from inventory can filter back to Date. Try changing in your measure code to use 'Date'[Date] instead of the inventory date, and use 'Date'[Date] in your visual too