Display Data for Last 3 months based on slicer selection

[Anonymous]

Hi, 

I am having a dim table  - Months, having the below values (until Dec-20) which is connected to multiple fact tables (one to many relationships) using a Month column. In those fact tables the data is until Jun-20.

I have in my model the Month column from Months table in a slicer so that the users, when they are selecting a certain month, the KPIs are being displayed for that particular month. Now, I have received an extra requirement: when the user selects a specific month, the KPIs to be retrieved also for the last 3 months of the selected month (I have the months on the X axis). Any ideas how can I achieve this? I have tried the below measure as filter, but it is showing only for the last 3 months with data (the slicer does not do anything). Thank you!

Within3MonthRange_Date = IF (

DATEDIFF(

MAX(Months[Month]),

     CALCULATE(

         MAX(Months[Month]), ALLSELECTED()),

MONTH) <= 2,

1,

0)

Jan-20

Feb-20

Mar-20

Apr-20

May-20

Jun-20

Jul-20

Aug-20

Sep-20

Oct-20

Nov-20

Dec-20

[parry2k]

@Sania-F not sure if I understood your question. You are already using disconnected table, what is the issue? What part is not working? Please clarify. Thanks!

[amitchandak]

@Anonymous ,

try like

Rolling 3 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-3,MONTH))
Rolling 3 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],max(Sales[Sales Date]),-3,MONTH))

In case you want to display last 3-month use relative date slicer

https://docs.microsoft.com/en-us/power-bi/visuals/desktop-slicer-filter-date-range

or refer this

https://www.youtube.com/watch?v=duMSovyosXE

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/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184


Appreciate your Kudos.

[Anonymous]

Hi @amitchandak ,

I am trying to create something similar using your solution above but my chart isn't showing the last x months data but the month selected. Any idea what could have went wrong?

MEASURE:

Total_ID2 =

VAR DateRange = DATESINPERIOD(DateTable[Date], MAX( DateTable[Date]), -12, month )

RETURN

CALCULATE(Count('Value Tracker'[ID]), TREATAS(DateRange, DateTable[Date].[Date]) )

RESULT and SLICER:

*Slicer is created using two independent table

Thanks!

[Anonymous]

Hi @amitchandak,

Unfortunately, I cannot use a relative slicer due to the other KPIs present in the page. Basically, I need to use a single selection slicer and the user to select only one month. So, having in mind the single month selection, in a parretto chart, for one of the KPIs, I need to retrieve the previous 3 months values. For instance, if the user selects June, he will see Apr, May and June data; if he selects March, he will see Jan, Feb and March data. Also, I cannot use a independent table with months (no relationship), because I have multiple measures/visuals in that page, from different fact tables, and they have only one linkage, the months column. 

Any other suggestion?

Thank you!

[amitchandak]

@Anonymous , I made three suggestions.

1. Rolling, Show data of 3 months in single month.

2. Relative date slicer - So not fit.

3. I shared a video , for any date how to share 12 Months  -https://www.youtube.com/watch?v=duMSovyosXE

All three did not work ?

Can you share sample data and sample output in table format?

[Anonymous]

@amitchandak please, check the details in my previous answer

[amitchandak]

@Anonymous , Oh Missed second part of answer. Let check with other superusers.

Hi @Greg_Deckler , @parry2k , Please suggest solution for this.

[parry2k · Accepted solution]

@Anonymous you have to use disconnected  table for this purpose, create a table for slicer and use value from that table to filter the data in your main table using TREATAS

Measure = 
VAR __dateRange = DATESINPERIOD ( DateDisconnected[Date], MAX( DateDisconnected[Date] ), -3, MONTH )
RETURN
CALCULATE ( SUM ( Table[Amount] ), TREATAS ( __dateRange, DateTable[Date] ) )

I haven't tested this but it will get you started if you are unable to figure out, share sample pbix file and can take a look.

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[Sania-F]

Hi @parry2k It worked for me with disconnected calendar. However I have an additional requiremnet- I need to display last 2 alternative months data. [ my need is date in slicer and date in matrix are cominf from same table - ]

when I select  mar, then matrix should display data of jan, mar. 

If I select apr, then display data of feb, april

If I select aug in slicer, matrix should display data of jun, aug.

Re: display data for last 2 alternative months whe... - Microsoft Power BI Community

The Dax worked for disconnected calendar. 

trial_month_Measure =

VAR __dateRange = DATESINPERIOD(DisconnectedCalendar[Date],max(DisconnectedCalendar[Date]),-3,MONTH)

Return CALCULATE ( SUM ( Orders[Sales] ), TREATAS ( __dateRange, Orders[Order Date]) )

DisconnectedCalendar =

var calTable = CALENDARAUTO()

Var MyCal =

    AddColumns( calTable,

                "Index", MONTH([Date]),

                "Month",FORMAT([Date],"mmm"),

                "qtr",format([Date],"\QQ"),

                "year",format([Date],"yyyy")

    )

Return

    MyCal

[amitchandak]

Thanks @parry2k .

@Anonymous 

I am hoping New version of reltive date slicer will come with date and will address this. I can see that already in progress. Not sure they are planning to give date in that. 

https://ideas.powerbi.com/ideas/idea/?ideaid=37cf4112-bb85-44fe-ba0d-188517c39a2b