Show only latest day of each month - Visual

[uie07923]

Hi guys,

I have a database with inventory values for each day. I don't necessarily have the data for all days.

I need to make a visual that shows only the latest date of each month.

As of now, users have to manually filter the last days available for each month.

As it is:

What I need:

[lauramirlyn1]

How can I apply this for multiple items. I have data with different amounts but I only need the last day of the month that was updated but I also have multiple offices in my dataset. So I need to filter by the last day of the month by office.

[mahoneypat]

Assuming you always have inventory values for the last DOM (vs. last working DOM), you can use a measure expression like this (also assuming you have a separate Date table, but you can use the same approach if not).

Last DOM =
VAR thisdate =
    MAX ( 'Date'[Date] )
VAR lastDOM =
    EOMONTH ( thisdate, 0 )
RETURN
    IF ( thisdate = lastDOM, [Total Sales] )

Pat

[uie07923]

Hi there! Thanks for the quick reply.

I don't always have the last DOM. It can be that the last working day of the month was not the last day per se.

[v-rzhou-msft · Accepted solution]

Hi @uie07923 

You can build a filter measure to achieve your goal.

I build a sample to have a test. YearMonth column is a calculated column.

YearMonth = YEAR(Sheet4[Date])*100+MONTH(Sheet4[Date])

Measure:

Filter = 
VAR _MaxDate =
    MAXX (
        FILTER ( ALL ( Sheet4 ), Sheet4[YearMonth] = MAX ( Sheet4[YearMonth] ) ),
        Sheet4[Date]
    )
RETURN
    IF ( MAX ( Sheet4[Date] ) = _MaxDate, 1, 0 )

Add this measure into visual level filter field and set it to show items when the value is 1. Result is as below.

Best Regards,

Rico Zhou

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

[uie07923]

You are the best, that's exactly what I needed! Thanks and have a great day!

Greetings from Brazil!