Forum Discussion

gcyr's avatar
gcyr
Frequent Visitor
8 years ago
Solved

Measure - End OF Month based on slicer

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...
Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    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's avatar
    v-yulgu-msft
    8 years ago

    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

Zubair_Muhammad's avatar
Zubair_Muhammad
Community Champion
8 years ago

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's avatar
gcyr
Frequent Visitor
8 years ago

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's avatar
    Zubair_Muhammad
    Community Champion
    8 years ago

    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