Measure to show average monthly sales

[emma313823]

Hi All

I'm kind of stuck figuring this out. I have a model I'm testing out and have created a DATE table. I have another table called Revenue Combined - see screenshot. I have a relationship created from the REVENUE COMBINED table to the DATE table. The revenue table has monthly revenue data points for years 2021-2024.

1. What I would like to do is create a measure for each year to show the monthly average sales for each year 2021, 2022, and 2023. I'm stuck on the syntax.

2. I'd like to create a rolling average measure for 2024 since there are still several months to go, so this value would change as the remainder of the year progresses. I'm hoping I can find some more automated way with DAX syntax to automatically change the month average as each month ends and a new one begins. I'm looking for the change to occur only after the month closes...so if June 30th passes, then on July 1 the rolling average would change to include average based on 6 months.

3. I'd like to also create a measure for runrate that I'm hoping could be more automated instead of me having to manually change a value each month. I'd like it to be a sum of all revenue months in 2024/# months completed*12 (i.e. assuming data in through May $2,174,204/5 months * 12 = $5,218,088). Would there be some way to have DAX syntax such that at the end of June the DAX would automatically change from 5 months to 6, thus changing the total runrate value as the year progresses?

Thanks

Emma

[emma313823]

Hi

Thank you so much for trying on this issue for me. You helped me get super close to what I need. 

Emma

[aduguid]

no worries, anytime 🙂

[aduguid]

First, it doesn't look like you have marked the calendar table as a date table.

1. Create the measure and then all you need to do is add the measure and the year from the calendar table.

Total Revenue = SUM('Revenue Combined'[Revenue]) + 0

2. Here is an example of the rolling average that you can reference your original measure

Rolling Average Current Year = 
CALCULATE(
    AVERAGEX(
        DATESINPERIOD(
            Calendar[Date],
            LASTDATE(Calendar[Date]),
            -30,
            DAY
        ),
        [Total Revenue]
    ),
    YEAR(Calendar[Date]) = YEAR(TODAY())
)

3. You can also reference your original measure for the YTD calculation

YTD Revenue = 
TOTALYTD(
    [Total Revenue],
    'Calendar'[Date]
)

[emma313823]

Hi 

Thanks so much for a quick response.

1. worked great, but can you tell me what the +0 does in the DAX syntax?

2. I had to modify the code to properly show my calendar table and it did not prompt for errors, however when I tried to drop the measure into a card to test, it shows zero. Do you think I did something wrong with this one?

Rolling Average Current Year =

CALCULATE(

    AVERAGEX(

        DATESINPERIOD(

            'Calendar_Dates'[Date],

            LASTDATE('Calendar_Dates'[Date]),

            -30,

            DAY

        ),

        [Total Revenue]

    ),

    YEAR('Calendar_Dates'[Date]) = YEAR(TODAY())

)

Emma

[aduguid]

1. In case you filter the measure and there are no records for the filter it will show 0 instead of BLANK(). I will usually do one measure for a card and another for things like a matrix. 

Total Revenue = SUM('Revenue Combined'[Revenue]) // for a matrix so it only show the values that are filtered

Total Revenue KPI = [Total Revenue] + 0 // for a kpi card so it doesn't show BLANK

2. Give this one a try

Rolling Average = 
    AVERAGEX(
        FILTER( ALLSELECTED('Calendar'),
            Calendar[Date] <= MAX(Calendar[Date]) ),
        [Total Revenue]
    )

[emma313823]

Hi 

hopefully i modified that correctly, but still yielding zero. After I created the measure and added to the card visual, I had no filters, as I thought this would yield current YTD.

Emma

[aduguid]

can you try this one?

Rolling Average = 
    AVERAGEX(
        FILTER( ALLSELECTED('Calendar'),
            Calendar[Date] <= MAX(Calendar[Date]) ),
        [Total Revenue]
    )

[emma313823]

well i'm closer now than i was before. the card is populating data. it shows $4.63K which is off a bit for some reason.

2024 sample has 5 months (jan-may), so the average for 5 months should be $434,841 (or on the card it should show as 4.35K.

Rolling Average Current Year =

    AVERAGEX(

        FILTER( ALLSELECTED('Calendar_Dates'),

            Calendar_Dates[Date] <= MAX(Calendar_Dates[Date]) ),

        [Total Revenue]

    )

[aduguid]

ok, let me give it one more go

Rolling Average = 
    AVERAGEX(
        FILTER( ALLSELECTED('Calendar'),
            Calendar[Date] <= MAX('Revenue Combined'[Date]) ),
        [Total Revenue]
    )

[emma313823]

Thanks so much for your help. You got me closer than I would have on my own. I'll plug away at this mystery and hopefully find a soluiton.

Thanks again

Emma

[aduguid]

I think I got it now.  Do you want to do an average of an average for the total? So that the total is an average of the rolling month's averages.

Is that correct?