Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
dBrand
Frequent Visitor

Rolling Average not including months with no data

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.

1 ACCEPTED SOLUTION
edhans
Super User
Super User

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.

edhans_0-1606779228400.png

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

12 REPLIES 12
edhans
Super User
Super User

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!



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
amitchandak
Super User
Super User

@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

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.

 

edhans_1-1606787663490.png

 

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
dBrand
Frequent Visitor

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.

wdx223_Daniel
Super User
Super User

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

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

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!

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.

edhans_0-1606789450757.png

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.

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
dBrand
Frequent Visitor

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

dBrand
Frequent Visitor

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. 

edhans
Super User
Super User

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.

edhans_0-1606779228400.png

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
dBrand
Frequent Visitor

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!

dBrand
Frequent Visitor

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors