How to display last 12 months and Previous 12 months with slicer

[harib]

Hi everyone,

I have a senario where i want to show last 12 months data and also if i want to show previous 12 months data using slicer.

2018-01
2018-02
2018-03
2018-04
2018-05
2018-06
2018-07
2018-08
2018-09
2018-10
2018-11
2018-12
2019-01
2019-02
2019-03
2019-04
2019-05
2019-06
2019-07

In the slicer i would like to display 3 values like ALL, Previous Month, Last 12 Months.

If i Select  Last 12 months from slicer it should show last 12 months data

If i Select  Previous 12 Months from slicer it should show Previous 12 months data

If i Select  All from slicer it should show All months data

If anyone know the solution please let me know. 

Thanks in Advance

[v-lid-msft]

Hi @harib ,

We can use a measure in visual filter after create a calculated table to meet your requirement:

Calculated table:

Slicer = DATATABLE("Value",STRING,{{"All"},{"Last 12 Months"},{"Previous Month"}})

Measure:

Show Contorl = 
var d = SELECTEDVALUE('Table'[Date])
return
SWITCH(SELECTEDVALUE('Slicer'[Value]),"Previous Month",IF(d >= DATE(YEAR(TODAY()),MONTH(TODAY())-1,1) && d <= DATE(YEAR(TODAY()),MONTH(TODAY()),1),1,-1),"Last 12 Months",IF(d >= DATE(YEAR(TODAY())-1,MONTH(TODAY()),1) && d  <= DATE(YEAR(TODAY()),MONTH(TODAY()),1),1,-1),1)

If it doesn't meet your requirement, Could you please show the exact expected result based on the Tables that we have shared.

Best regards,

[harib]

@v-lid-msft 

Exactly matching with my requirment, but in my case Year-Month (2019-01) in text format. Its' not filtering. Getting below error . Can u please let me know the solution.

Thanks in advacne

[v-lid-msft]

Hi @harib ,

We can try to use the following measure to meet your requirement:

Show Contorl =
VAR YM =
    SELECTEDVALUE ( 'Table'[Year-Month] )
VAR d =
    DATE ( VALUE ( LEFT ( YM, 4 ) ), VALUE ( RIGHT ( YM, 2 ) ), 1 )
RETURN
    SWITCH (
        SELECTEDVALUE ( 'Slicer'[Value] ),
        "Previous Month", IF (
            d
                >= DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) - 1, 1 )
                && d <= DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), 1 ),
            1,
            -1
        ),
        "Last 12 Months", IF (
            d
                >= DATE ( YEAR ( TODAY () ) - 1, MONTH ( TODAY () ), 1 )
                && d <= DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), 1 ),
            1,
            -1
        ),
        1
    )

Best regards,

[harib]

@v-lid-msft 

I have tried with you function, but here when i select "Previous month" i't showing only 2020-01, 2020-02, records only, if i select  "Last 12 months"  it's showing all months data include 2020-01, 2020-02. 

I have added 12 in the place of 1 , then it's showing last 12 months data when i select Previous Month, but if i select  Last 12 months it's not showing Remaning months.

Show Contorl =
VAR YM =
SELECTEDVALUE ( 'Date'[Month_Year(NPR)] )
VAR d =
DATE ( VALUE ( LEFT ( YM, 4 ) ), VALUE ( RIGHT ( YM, 2 ) ), 1 )
RETURN
SWITCH (
SELECTEDVALUE ( 'Slicer'[Value] ),
"Previous Month", IF (
d
>= DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) - 12, 1 )
&& d <= DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), 1 ),
1,
-1
),
"Last 12 Months", IF (
d
>= DATE ( YEAR ( TODAY () ) - 1, MONTH ( TODAY () ), 1 )
&& d <= DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), 1 ),
1,
-1
),
1
)

Can u plese check once and let me know. 

Thanks.

[v-lid-msft]

Hi @harib ,

Sorry for that we misunderstand the meaning of the last 12 month, we can use the following measure to meet your requirement:

Show Contorl = 
VAR YM =
    SELECTEDVALUE ( 'Date'[Month_Year(NPR)] )
VAR d =
    DATE ( VALUE ( LEFT ( YM, 4 ) ), VALUE ( RIGHT ( YM, 2 ) ), 1 )
Var MaxYM = CALCULATE(MAX('Date'[Month_Year(NPR)]),ALLSELECTED('Date'))
   var LastMonth =  DATE ( VALUE ( LEFT ( MaxYM, 4 ) ), VALUE ( RIGHT ( MaxYM, 2 ) ), 1 )
RETURN
    SWITCH (
        SELECTEDVALUE ( 'Slicer'[Value] ),
        "Previous Month", IF (
            d
                >= DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) - 12, 1 )
                && d <= DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), 1 ),
            1,
            -1
        ),
        "Last 12 Months", IF (
            d
                >= DATE ( YEAR (LastMonth ) - 1, MONTH (LastMonth), 1 )
                && d <= LastMonth,
            1,
            -1
        ),
        1
    )

Best regards,

[harib]

@v-lid-msft 

Sorry to troubling you again, Last 12 months showing exacty what i was looking, but when we select Previous month, it it should show from 2019-04 to 2018-04 (12 months. i mean before last 12 months ) I hope it's clear now.

[v-lid-msft · Accepted solution]

Hi @harib ,

Thank you for it, it is very clear now, please try to use the following measure:

Show Contorl =
VAR YM =
    SELECTEDVALUE ( 'Date'[Month_Year(NPR)] )
VAR d =
    DATE ( VALUE ( LEFT ( YM, 4 ) ), VALUE ( RIGHT ( YM, 2 ) ), 1 )
VAR MaxYM =
    CALCULATE ( MAX ( 'Date'[Month_Year(NPR)] ), ALLSELECTED ( 'Date' ) )
VAR LastMonth =
    DATE ( VALUE ( LEFT ( MaxYM, 4 ) ), VALUE ( RIGHT ( MaxYM, 2 ) ), 1 )
RETURN
    SWITCH (
        SELECTEDVALUE ( 'Slicer'[Value] ),
        "Previous Month", IF ( d <= DATE ( YEAR ( LastMonth ) - 1, MONTH ( LastMonth ), 1 ), 1, -1 ),
        "Last 12 Months", IF (
            d
                >= DATE ( YEAR ( LastMonth ) - 1, MONTH ( LastMonth ), 1 )
                && d <= LastMonth,
            1,
            -1
        ),
        1
    )

If you do not want to include the 19-04 in the Previous month, we can use the following measure:

Show Contorl =
VAR YM =
    SELECTEDVALUE ( 'Date'[Month_Year(NPR)] )
VAR d =
    DATE ( VALUE ( LEFT ( YM, 4 ) ), VALUE ( RIGHT ( YM, 2 ) ), 1 )
VAR MaxYM =
    CALCULATE ( MAX ( 'Date'[Month_Year(NPR)] ), ALLSELECTED ( 'Date' ) )
VAR LastMonth =
    DATE ( VALUE ( LEFT ( MaxYM, 4 ) ), VALUE ( RIGHT ( MaxYM, 2 ) ), 1 )
RETURN
    SWITCH (
        SELECTEDVALUE ( 'Slicer'[Value] ),
        "Previous Month", IF (
            d
                < DATE ( YEAR ( LastMonth ) - 1, MONTH ( LastMonth ), 1 )
                && d
                    >= DATE ( YEAR ( LastMonth ) - 2, MONTH ( LastMonth ), 1 ),
            1,
            -1
        ),
        "Last 12 Months", IF (
            d
                >= DATE ( YEAR ( LastMonth ) - 1, MONTH ( LastMonth ), 1 )
                && d <= LastMonth,
            1,
            -1
        ),
        1
    )

Best regards,

[harib]

@v-lid-msft 

Superb, It's perfectly working fine. Thanks a lot for your dedication on this task.  😀 🤗 

[amitchandak]

Please refer for the slicer part : https://community.powerbi.com/t5/Desktop/Slicer-MTD-QTD-YTD-to-filter-dates-using-the-slicer/td-p/50...

In case you are using a date calendar joined with you fact following will help

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
last year MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-12,MONTH))))

MTD (Year End) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFYEAR('Date'[Date])))
MTD (Last Year End) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFYEAR(dateadd('Date'[Date],-12,MONTH),"8/31")))
Rolling 3 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-3,MONTH)) 

Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date Filer],MAX(Sales[Sales Date]),-12,MONTH))  
3 month back MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-3,MONTH)))
 


QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(('Date'[Date])))

Last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,QUARTER)))
Next QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],1,QUARTER)))

Last year same QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,Year)))





YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
Last YTD complete Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
2 Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-2,Year))

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(date,date[date] <=maxx(date,date[date])))

Cumm Sales = CALCULATE(SUM('Fact table'[Sales]),filter(date,date[date] <=maxx(date,date[date]) 
	&& weekday(sales[date]) <=weekday(maxx(date,date[date])) ))


Last YTD complete Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Last to last YTD complete Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-2,Year)),"12/31"))


Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-12,MONTH))  
Rolling last 12 before 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(dateadd('Date'[Date],-12,MONTH)),-12,MONTH))  
Rolling last 1 before 1 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],Maxx('Date',dateadd('Date'[Date],-1,MONTH)),-1,MONTH))  
	Rolling 1 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],max(Sales[Sales Date]),-1,MONTH))  

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s.

Refer
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi

https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin

[harib]

@amitchandak 

Those functions are individual measures. My requiremtent is using slicer i want to display the values. 

Thanks 

[amitchandak]

Hope you checked, this does not work

Please refer for the slicer part : https://community.powerbi.com/t5/Desktop/Slicer-MTD-QTD-YTD-to-filter-dates-using-the-slicer/td-p/50...