Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Display Data for Last 3 months based on slicer selection

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 th...
Need Help
Show and Tell
Tips and Tricks
  • parry2k's avatar
    parry2k
    6 years ago

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

parry2k's avatar
parry2k
Icon for Super User rankSuper User
6 years ago

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's avatar
Sania-F
Icon for Resolver I rankResolver I
3 years ago

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