Hi, I have a report that now always shows a measure on a Year-Month axis, with a calculated column on the Date table that determines the last 6 months acting as a filter to always show the last 6 months. This calculated column is based on Today().
However, the report now needs to change in such way that the graph with 6 months on the x-axis doesn't necessarily display the last 6 months relative to today, but also needs to be able to show 6 months prior to a chosen date.
So, whereas the users usually run a report over the last month, let's say July. It would show monhtly statistics concerning the month of july and some trend graphs that show Feb-July and also some 13 month graphs that show July 2019 - July 2020.
This works as long as the report is made in Month X and the period they want to run the report for is Month X-1
However I can't get the new requirement to work that if we live in August 2020, and they want to report statistics over April 2020, that the 6-month period changes to Nov 2019, Dec 2019, Jan 2020, Feb 2020, Mar 2020, April 2020.
What I tried so far:
- Keep it simple and add another filter and instruct user to use both a report period (eg April 2020) and a trendperiod filter (eg Nov 2019 - April 2020). However I then ran into the 13months graphs and it gets a bit rediculous to ask the user to set 3 date filters.
- Use a measure with DATESINPERIOD with MAX(Dates) and interval of 6 Months. And then rather of using the Dates table to add Year-Month to the axis, I use the Year-Month of the date column of the fact table. This doesn't however work for graphs that shows 2 measures, both based on different dates (creation date and closure date). So I cannot use this solution: http://sqljason.com/2018/03/display-last-n-months-selected-month-using-single-date-dimension-in-powe...
What I wanted to try and what should otherwise work I think is to promt the user with a parameter field where they enter the month they want to report on. And then with calculated column in Date table dynamically mark the date rows that qualify as being within 6 months prior to the parameter. However, as I am using Live Connection to Azure Analysis Services, the What-If parameter is greyed out and data is loaded on the Azure side.
It is a shame that Power BI does not provide capability of setting date filters based on other fields, and relative dates only seem possible relative to Today, rather than relative to user-specified dates.
Anyone who has encountered and solved this issue?
