cancel
Showing results for
Did you mean:
Frequent Visitor

## Show only latest day of each month - Visual

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:

1 ACCEPTED SOLUTION
Community Support

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.

4 REPLIES 4
Super User

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 ( thisdate0 )
RETURN
IF ( thisdate = lastDOM, [Total Sales] )

Pat

Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

@mahoneypa HoosierBI on YouTube

Frequent Visitor

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.

Community Support

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.

Frequent Visitor

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

Greetings from Brazil!

Announcements