topic Re: Rolling Average not including months with no data in DAX Commands and Tips

Rolling Average not including months with no data

dBrand — Mon, 30 Nov 2020 21:17:10 GMT

I am looking to create a measure that returns the Rolling Average of 3 months. But I do not want to have it include months that have no transactions. So, if months 1 and 3 have data, but not month 2, I would add up months 1 and 3 and divisde by 2, not 3. But if all three had data, then it would divide by three. I already have measures that return the totals for each month, and thought I could use that, but am puzzled out how to come up with the divisor. Any help would be greatly appreciated.

Another approach I was trying, if the above has no solution, was to count the number of transactions for each mnth and then for those not equal to zero, come up with the months with a valuse and use that to count the months with data. But that is messy sounding and I still couldn't figure out how to do that either. Ugh.

Re: Rolling Average not including months with no data

edhans — Mon, 30 Nov 2020 23:36:56 GMT

Try this dBrand

3 Mo Rolling Average = VAR varCurrentSalesMonth = MAX( Sales[Date] ) VAR varCurrentMonth = MAXX( FILTER( 'Date', 'Date'[Date] = varCurrentSalesMonth ), 'Date'[Month Year Sort] ) VAR varValidDateRanges = FILTER( ALL( 'Date' ), 'Date'[Month Year Sort] <= varCurrentMonth && 'Date'[Month Year Sort] >= varCurrentMonth - 2 ) VAR varRolling3MonthsSales = CALCULATE( SUM( Sales[Sales] ), varValidDateRanges ) VAR varValidMonths = CALCULATE( DISTINCTCOUNT( 'Date'[Month Year Sort] ), FILTER( varValidDateRanges, CALCULATE( SUM( Sales[Sales] ) ) <> BLANK() ) ) RETURN DIVIDE( varRolling3MonthsSales, varValidMonths, 0 )

It requires a date table. My Month Year Sort column is an integer in my date table that is YYYYMM, so 202001 for Jan, 202002 for Feb, etc. this gives me a good integer to move up/down by one month in the table by simpliy adding/subtracting 1. I also used it to count the months with data.

You can see there is no data for feb, so the march value of 17 is the (18+16)/2 months, March and Jan.
Same for August. (9+7)/2 = 8 because there is no July 2020 numbers. But Nov is (7+9+10)/3 = 8.666, shown as 9 due to how I have my data formated.

If you want to use my date table, here you go. Full instructions there for plopping that into Power Query. Just mark it as a date table once it loads.

Re: Rolling Average not including months with no data

wdx223_Daniel — Tue, 01 Dec 2020 00:22:34 GMT

dBrand AverageX can omit the months which have not data automatically. such as

=averagex(values(yearmonth),[measure])

Re: Rolling Average not including months with no data

amitchandak — Tue, 01 Dec 2020 01:28:34 GMT

dBrand , Try a formula like

Rolling 3 = divide( CALCULATE(sum(Sales[Sales]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date]),-12,MONTH)) ,
CALCULATE(distinctCOUNT('Date'[Month Year]),DATESINPERIOD('Date'[Date],MAX('Date'[Date]),-12,MONTH), not(isblank((Sales[Sales])))))

the denominator is checking is data in there for numerator or not

Re: Rolling Average not including months with no data

edhans — Tue, 01 Dec 2020 02:00:14 GMT

did you try that amitchandak ?- I fixed the -12 to be -3, but it doesn't provide the same or correct results as I understood dBrand to request it. I think yours is just a Rolling 3 month average template, but doesn't take into account missing months.

Re: Rolling Average not including months with no data

dBrand — Tue, 01 Dec 2020 02:17:52 GMT

I've heard that. But how does this know what three months I want? The filter on the page is set for the Month/year. Thanks!

Re: Rolling Average not including months with no data

dBrand — Tue, 01 Dec 2020 02:21:02 GMT

I haven't tried yet, but you are correct in your understanding. I need to create some smaple data to test these, as I only have data for one month at the moment with the real data. I will post back once tested. Thanks.

Re: Rolling Average not including months with no data

dBrand — Tue, 01 Dec 2020 02:22:49 GMT

Thanks. Will look to try this out. More code than I would have hoped is necessary for the solution, but hey, that's how coding goes.

Re: Rolling Average not including months with no data

edhans — Tue, 01 Dec 2020 02:24:54 GMT

What filter dBrand - I don't see that in your original request.

However, my measure handles it without modification. I've selected march, and it is still returning the correct Jan-Mar average over 2 months since Feb is empty.

If that isn't what you need, can you please give us the full requirements with perhaps some data and screenshots?

How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.

Re: Rolling Average not including months with no data

dBrand — Tue, 01 Dec 2020 02:33:24 GMT

THanks for the quick reply. I will have to follow up with what you ask tomorrow (out of time today, sorry). But it's a date filter where they can select one month on the page. I really would prefer to do it with the simple command you have used. I just am not sure how it knows to only take 3 months into account, but I'm sure I'm missing something - I'm still figuring all this out.

Re: Rolling Average not including months with no data

dBrand — Tue, 01 Dec 2020 03:18:58 GMT

Mixed up my replies. I will try your initial solution tomorrow, as that is what I was after. Thanks.

Re: Rolling Average not including months with no data

dBrand — Tue, 01 Dec 2020 14:59:50 GMT

THis seems to work. THank you for the help! Wish they would make things like this easier. I had thought using Average() would be useful, but nothing is ever that easy. Thanks again!

Re: Rolling Average not including months with no data

edhans — Tue, 01 Dec 2020 15:04:17 GMT

Glad I was able to help dBrand
When I need an average, I often only use an Average* function about 70% of the time. Sometimes you have to manually get the numerator and denominator for special cases like this. It may be possible to use average or averagex here, but still a lot of filters to remove and reapply based on your 3 calendar month requirement.

Hope your project goes smoothly!