Forum Discussion
Display Data for Last 3 months based on slicer selection
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.⚡
Super UserAnonymous ,
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.
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?
amitchandak please, check the details in my previous answer
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 )RETURNCALCULATE(Count('Value Tracker'[ID]), TREATAS(DateRange, DateTable[Date].[Date]) )RESULT and SLICER:*Slicer is created using two independent tableThanks!