Measure - End OF Month based on slicer

[gcyr]

Hi,

I'm trying to create a measure that will vary based on a slicer selection.

My table has 3 columns. (Date - Index Name - Value)

Currently, I only have 3 index (Index A, Index B & Index C)

I'm trying to get the last data available by month based on the selection of the user in the slicer. The slicer contains the Index Name.

The results I'm trying to get are :

Index A : 2017-09-26

Index B : 2017-09-19

Index C : 2017-09-18

I tried using ENDOFMONTH(Date) but it always returns 2017-09-26 for each Index.

Thanks,

Guillaume

[Zubair_Muhammad · Accepted solution]

Hi Guillaume,

Check this one please

=Calculate(Max(Table1[Date]),filter(Table1,Table1[IndexName]=earlier(Table1[IndexName])&&Month(Table1[Date])=Month(Earlier(Table1[Date]))))

Regards,

Zubair

[v-yulgu-msft · Accepted solution]

Hi @gcyr,

You can also try this workaround:

Create a Month column in your source table: Month = Table1[Date].[Month]

Create a measure like: Last Date = MAX(Table1[Date])

Then, add [Index Name], [Month] and [Last Date] into table visual. It will automatically display last date per Index per month.

Best regards,
Yuliana Gu

[gcyr]

Hi,

Both your solution worked.

Thanks!

Guillaume

[Zubair_Muhammad]

Hi Guillaume,

Are you trying a  calculated column or a measure?

Try below in a calculated column. I m not sure if this is what you need

=Calculate(Max(Table1[Date]),filter(Table1,Table1[IndexName]=earlier(Table1[IndexName])))

[gcyr]

Hi @Zubair_Muhammad

Your formula works for the current month but not for the previous months

For example in August, I have data for all three indexes up to August 31.

Therefore for the month of September I was expecting

Index A : 2017-09-26

Index B : 2017-09-19

Index C : 2017-09-18

And for the month of August (Similar treatment for all preivous months)

Index A : 2017-08-31

Index B : 2017-08-31

Index C : 2017-08-31

I don't know if a measure of a calculated column is better for my request.

Thanks,

Guillaume

[Zubair_Muhammad · Accepted solution]

Hi Guillaume,

Check this one please

=Calculate(Max(Table1[Date]),filter(Table1,Table1[IndexName]=earlier(Table1[IndexName])&&Month(Table1[Date])=Month(Earlier(Table1[Date]))))

Regards,

Zubair