Divide by number of days in the month

[DieLem]

Hi,

I would like to determine the average sales per month, based on calendar days in the month. The logic is straightforward, total number of sales in a month, divide by number of days. As seen in my example below for February it should 1400/28 = 50 (Light blue column in screenshot below)

Once I have the average, I want to plot that per month on a timeline. I also need the current month to divide by only the amount of days that has expired. Thus if it's 15th of April, divide by 15 and not 30.

I have date dimention table and a table with the days per month that I have linked to the date dim on monthname. However when I create a measure I don't know how to join on the month and divide by the correct number of days.

Thanks!

[v-juanli-msft · Accepted solution]

Hi @DieLem 

Create measures

Measure =
CALCULATE (
    COUNT ( 'calendar'[Date] ),
    FILTER (
        ALL ( 'calendar' ),
        'calendar'[year] = MAX ( 'calendar'[year] )
            && 'calendar'[month] = MAX ( 'calendar'[month] )
            && 'calendar'[Date] <= TODAY ()
    )
)

Measure 2 = SUM('Table'[sales])/[Measure]

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.