Having trouble implementing a complex visal filter that relies on two variables

[Domenick]

I'm creating a report that shows year-over-year financial data. I want to be able to show Year-to-date data compared to the similar time frame for previous years. So for example, if I have this year's (2020) data up til February, then I would show that as well as last year's (2019) data up til February. You would see January and February of 2019 compared to January and February of 2020.

Unfortunately, I also need to check an additional field to find the most recent month where the financial period has been closed. So even though it might be April, I might only be able to show data up til February (which means I would only want to show data from current and previous year up to February). If we are in January and I only have data up til December of last year, then I would want to show all of last year (Jan - Dec) and all of the data from two years ago. So my logic needs to follow: find most recent month for which the period has ended, show all data from the beginning of that year to the most recent period for which the period has ended, show data from the previous year up to that same month.

Does anyone have any ideas on how to accomplish this? Visual filters don't allow us to pull in variables/parameters, so I would need some sort of work-around.

Here's a sketch of how the mentioned data looks. "Closed" is either Y or N. I need to pay attention to the most recent period where Closed is Y. Goal is to sum the Amount for all Accounts per year for months that are less than the most recently closed period's month.

[Tad17]

Hey @Domenick 

I suggest playing with the following built in functions of Power BI:

Total Month to Date: https://docs.microsoft.com/en-us/dax/totalmtd-function-dax

Total Year to Date: https://docs.microsoft.com/en-us/dax/totalytd-function-dax

Same Period Last Year: https://docs.microsoft.com/en-us/dax/sameperiodlastyear-function-dax

As for having a slicer that selects the most recently closed month, I recommend either using a timeline splicer and adjusting it as needed, added a filter to all pages, or researching dynamic splicers such as the one described here: https://community.powerbi.com/t5/Desktop/Dynamic-slicer-selection/td-p/475638

Dynamic Splicers are finicky, so you will have to play with the concept to fit your needs. I personally prefer just having a timeline splicer (this one: https://appsource.microsoft.com/en-us/product/power-bi-visuals/WA104380786?tab=Overview), so that I can adjust it to the period I wish to visualize and analyze.

If this helps please accpet it as a solution. We can work on other issues you have and create more solutions if necessary.

[v-chuncz-msft]

@Domenick 

You may use VAR, CALCULATE and MAX to add a measure.

• https://www.sqlbi.com/articles/variables-in-dax/
• https://www.sqlbi.com/articles/filter-arguments-in-calculate/
• https://www.sqlbi.com/articles/compare-equivalent-periods-in-dax/
• https://www.sqlbi.com/articles/time-intelligence-in-power-bi-desktop/

[Domenick]

Thanks for the reply, but I don't see how those functions would help me a achieve what I need.