Rolling 12 month Visuals with Slicer for Dates Outside the Period

[Nick555]

I have a report with 4 visuals. The requirement was that each visual on the page represent the last 12 rolling months. To do that I used my calendar table. I have a field called offset month. This basically counts the months backwards and forward by setting the current index month to 0. So I selected the months -1, -2, etc thru -12. This worked perfectly.

My issue is that a filter is requested to be able to select dates outside this time period. The file contains the needed data for the filter outside this rolling 12 month period. My issue is that when I try to apply the filter for dates outside of this rolling 12 month period the visuals don’t display anything. How do I fix this? Any ideas?

I will preface this by saying that my dax skills are level 0. (I just bought a book to try and learn dax but that’s no use to me given the problem needs to be resolved now.) I will add that I am also quickly coming to the realization that perhaps my power bi skills are pure garbage as well.

Any help will be greatly appreciated but hopefully you can explain it to me like I’m 5.

[danextian]

Hi @Nick555 

You will need a disconnected table to select the period from and a measure to return the values with x periods from what's been selected. The attached pbix has a few samples related to your use case.

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[Nick555]

I'm hoping this will work. Let me know if it doesn't.
Sample Data 

[lbendlin]

yes that worked.  Please give an example of " a filter is requested to be able to select dates outside this time period".  This will likely require a disconnected table.

What do you expect to show once those dates are selected?

[Nick555]

An example would be that the default state of the chart would be to display the 12 rolling months but if the user selected the filter icon in the upper right then the filter panel will ope. From there they should be able to select the datesthey are looking for. The filter pane that opens should allow you to narrow all of the visuals to whaever the filters are selected. For example, if the user selects 7/1/22-6/30/23 & Full time, and the Advertising Department, & the reason of Ret (retired); then the visuals should reflect that. Hopefully I'm making sense.

[Anonymous · Accepted solution]

Hi,

Thanks for the solution lbendlin  and danextian  offered, and i want to offer some more information for user to refer to.

hello @Nick555 , based on your description, you can consider to use crossfilter() function, you can refer to the following link about the function.

CROSSFILTER function - DAX | Microsoft Learn

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

You are aware that there is already a filter pane on the right, yes?  What's the value add of yet another filter pane?