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 achi...
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

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