Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
Jan-20 |
Feb-20 |
Mar-20 |
Apr-20 |
May-20 |
Jun-20 |
Jul-20 |
Aug-20 |
Sep-20 |
Oct-20 |
Nov-20 |
Dec-20 |
Solved! Go to 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.⚡
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@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!
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@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.
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:
Thanks!
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!
@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 , Oh Missed second part of answer. Let check with other superusers.
Hi @Greg_Deckler , @parry2k , Please suggest solution for this.
@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.⚡
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
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.
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
User | Count |
---|---|
93 | |
83 | |
77 | |
73 | |
66 |
User | Count |
---|---|
115 | |
104 | |
93 | |
64 | |
61 |