Need to display rolling 6 months data in each month bar.

[Anonymous]

Hi There, I have learnt a lot from the community. Thanks for that first. This is my first post & I have a query.

I have a bar chart which displays each month total. [Count of users]
My date filters are from the same table (its a Date Range with slider).
I need to show rolling 6 months data in each month bar.
Eg. If i select a date range in the filter (01-01-2021 to 30-06-2021) the following is expected
June 2021 bar should display Jan 2021 to May 2021 aggreagate data , and
May 2021 data bar should display December 2020 to April 2021 show aggreagate data and so on.
Every month bar should have the previous 6 months data in it.

I have tried writing a measure as follows

010 Moving Month = IFERROR(CALCULATE(DISTINCTCOUNTNOBLANK(Append2[USER_EMAIL]),DATESINPERIOD(Append2[Date(Key1)].[Date],LASTDATE(Append2[Date(Key1)].[Date]),-6,MONTH))+0,0)

When i add this measure to the column value it displays zero.

If i display this measure in a card and use the date filter as dropdown and select particular month and year this displays the last 6 months values from June 2021

Thanks in advance.

Note: I am a beginner 🙂

[daxer-almighty · Accepted solution]

// First of all, a correct model in PBI
// NEVER uses the auto-generated date
// tables and date/time hierarchies. They
// are---simply put---wrong. That's the first thing.
// Second, in a good model, one where
// there's a proper Date table as outlined
// in, say, https://dax.guide/dateadd, you
// can write:

[User Count 6M-Sum] =
// Grab the last date visible in the
// current context
var LastVisibleDate = MAX( Dates[Date] )
// Create a new filter on Dates
// that spans a period of 6 months
// which ends on the LastVisibleDate.
var NewDateFilter =
    DATESINPERIOD(
        Dates[Date],
        LastVisibleDate,
        -6, MONTH
    )
var Result =
// Apply the new filter and remove
// all original filters from Dates.
    CALCULATE(
        [User Count],
        NewDateFilter,
        REMOVEFILTERS( Dates )
    )
return
    Result

[Anonymous]

Thank a lot. That worked prefectly. 🤗