Get Last 6 months

[RaffiKouzoudj]

Hi all,

I have a date filter at the level of the page (on the filter pane), and I want to get the last 6 months of the date chosen from the filter. That is if I choose 31/03/2021 then I need to have the date Oct, Nov, Dec 2020 and then Jan, Feb and March 2021. I am using the below query however it is not working all the time.

Your help is really appreciated. thank you.

Last6months = V_ticketing_PBI[DateReceive]>=EDATE(EOMONTH(maxx(allselected('V_ticketing_PBI'), V_ticketing_PBI[DateReceive]),0),-6)

[v-kkf-msft · Accepted solution]

Hi @RaffiKouzoudj ,

First change the slicer setting to "Before". 

Then create the following formula:

Measure = 
var maxdate = 
    MAXX(
        ALLSELECTED(V_ticketing_PBI),
        V_ticketing_PBI[DateReceive]
    )
return 
    CALCULATE(
        SUM(V_ticketing_PBI[value]),
        FILTER(
            V_ticketing_PBI,
            V_ticketing_PBI[DateReceive] > EDATE(EOMONTH(maxdate,0),-6)
        )
    )

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

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

[amitchandak]

@RaffiKouzoudj , if you need only one row in time or 6 month rolling you can try a measure like this example

Rolling 6 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-6,MONTH))

But if you want to have a trend of six months, you need an independent date table too. refer my video

Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI