Issue with applying filters to last X amount of months

[Anonymous]

Hi all,

Struggling with this one for a while.

Assuming I want to see monthly data from the last 6 months, using the relative date slicer. I have 2 options:
1. Last 6 months - This actually translates to last ~180 days. It returns the data of the last partial month, but it does not return the full data of the first month in the timeframe 

2. Last 6 calendar months - This returns full data of the previous 6 months, but nothing for the current partial month


I need to be able to have a filter on the page, that shows full data for months 1-5 + partial data for month 6 in the timeframe. 
The only workaround I found was to apply the Top 6 months/period to each visual separately. Not fun. 

Any ideas?
Thanks!

[v-jayw-msft]

Hi @Anonymous ,

Good idea, already vote for it.

Best Regards,

jay

[mahoneypat]

You can write a measure expression like this to get your desired date range.

Prev Six Months =
VAR thisdate =
    MAX ( 'Date'[Date] )
VAR startdate =
    EOMONTH (
        thisdate,
        -7
    ) + 1
RETURN
    CALCULATE (
        [MinMaxDates],
        FILTER (
            ALL ( 'Date'[Date] ),
            'Date'[Date] <= thisdate
                && 'Date'[Date] >= startdate
        )
    )

Note you can also use a measure like to this to see which date range you are returning.  Use the name of this measure instead of [Total Sales] above and then replace with your final measure when you know you have the dates you want.

MinMaxDates = Min('Date'[Date]) & "-" & MAX('Date'[Date])

Regards,

Pat

[Anonymous]

Hi @mahoneypat, 

Thanks for your help. I am looking for a dynamic way, in which users that filter my report will be able to get any last x amount of months, including full data for complete months in the time range + data of the current month. 
For last 6 months, 5 full months + partial of month 6
For last 10 months, 9 full months + partial of month 10
ETC...

This can be achieved on a visual level, using Top 10 month, or top 6 month. But the "Top" functionality is not working on a page level.

Bottom line, the selection should be dynamic, not hardcoded in DAX

[mahoneypat]

You can add a What-If Parameter to your report so people can choose the number of months, and then use the value from that in place of the "-7" in that measure.

Regards,

Pat

[Anonymous · Accepted solution]

I have added this idea to the Ideas forum
https://ideas.powerbi.com/ideas/idea/?ideaid=bc643b1c-c626-eb11-8441-281878bda84d

This is something that should work out of the box, without any workarounds
Please vote!

Thanks

[mahoneypat]

Good idea.  Did it work for you and you can mark it as the solution?

Regards,

Pat