Dynamic rollback to previous 12 months based on date

[ShivaPrasad1]

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

[v-cherch-msft]

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]

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 · Accepted solution]

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