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

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