Forum Discussion

ShivaPrasad1's avatar
ShivaPrasad1
Icon for Helper I rankHelper I
8 years ago
Solved

Dynamic rollback to previous 12 months based on date

Hello Team,

 

I have date column of text format where values will be like 2017.12, 2018.01, 2018.12...I want to create a report for which I need to show last 12 months of data only. How can we achieve this requirement to change the values according to current date dynamically?

 

Ex:

Current Month: Aug 2018

Requied report data: Aug 2017 - Jul 2018 (2017.08 - 2018.07 column)

 

Current Month: Mar 2018

Requied report data: Mar 2017 - Feb 2018 (2017.03 - 2018.02 column)

 

 

Thanks in advance.

 

Regards,

Shiva

Need Help
  • v-cherch-msft's avatar
    v-cherch-msft
    8 years ago

    Hi ShivaPrasad1

     

    For current month, you may try below measure:

    Measure  =
VAR a =
    DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) - 1, 1 )
RETURN
    IF (
        MAX ( 'Table'[YearMonth.No] )
            IN DATESINPERIOD ( 'Table'[YearMonth.No], a, -12, MONTH ),
        1
    )

    Regards,

    Cherie

3 Replies

  • v-cherch-msft's avatar
    v-cherch-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    Hi ShivaPrasad1

     

    You may try to add a column to format the text column to date column as below:

     

    YearMonth.No =
DATE ( LEFT ( 'Table'[YearMonth], 4 ), RIGHT ( 'Table'[YearMonth], 2 ), 1 )

     

    Then create a measure as requested. For example:

    MonthSelect =
VAR a =
    DATE ( YEAR ( SELECTEDVALUE ( 'Current'[Current Month] ) ), MONTH ( SELECTEDVALUE ( 'Current'[Current Month] ) ) - 1, 1 )
RETURN
    IF (
        MAX ( 'Table'[YearMonth.No] )
            IN DATESINPERIOD ( 'Table'[YearMonth.No], a, -12, MONTH ),
        1
    )

     

     

    Regards,

    Cherie

    • ShivaPrasad1's avatar
      ShivaPrasad1
      Icon for Helper I rankHelper I
      8 years ago

      Thanks you v-cherch-msft

       

      But we don't have any filter to select current month. It should be change based on the current date. 

       

      Regards,

      Shiva

      • v-cherch-msft's avatar
        v-cherch-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        8 years ago

        Hi ShivaPrasad1

         

        For current month, you may try below measure:

        Measure  =
VAR a =
    DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) - 1, 1 )
RETURN
    IF (
        MAX ( 'Table'[YearMonth.No] )
            IN DATESINPERIOD ( 'Table'[YearMonth.No], a, -12, MONTH ),
        1
    )

        Regards,

        Cherie