topic Need to display rolling 6 months data in each month bar. in DAX Commands and Tips

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

Anonymous — Thu, 01 Jul 2021 17:09:42 GMT

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 🙂

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

daxer-almighty — Thu, 01 Jul 2021 20:46:13 GMT

// 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

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

Anonymous — Fri, 02 Jul 2021 06:10:57 GMT

Thank a lot. That worked prefectly. 🤗