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'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

 

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

5 Replies

  • 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

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee
    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

    • gcyr's avatar
      gcyr
      Frequent Visitor
      8 years ago

      Hi,

       

      Both your solution worked.

       

      Thanks!

      Guillaume