Actuals vs. Day to day average

[IvanS]

Hi guys,

I am desperately looking for help. I have table with invoices and calculated date table.

FACT_Invoices:

Invoice Date	Invoice Ref.	Invoice Amount
15.5.2023	ABC	100
1.6.2023	DEF	200
1.1.2023	GHI	300

I would need to visualize following:

- Invoice amount on daily basis for actual month vs. Average invoice amount on daily basis for last 3 calendar months

- Cumulative invoice amount for actual month vs. Average cumulative invoice amount for last 3 calendar months

Simply said, logic is following:

Take data from 1.6.2023 and compare it with average from 1.5.2023, 1.4.2023 and 1.3.2023.

Take data from 2.6.2023 and compare it with average from 2.5.2023, 2.4.2023 and 2.3.2023.

Take data from 3.6.2023 and compare it with average from 3.5.2023, 3.4.2023 and 3.3.2023.

...

Take data from 30.6.2023 and compare it with average from 30.5.2023, 30.4.2023 and 30.3.2023.

But, due to different number of days in month, it needs to consider following scenarions:

• Take data from 31.7.2023 and compare it with average from 31.6.2023, 31.5.2023 and 31.4.2023 (it should take from last 3 calendar months only those which have 31 days).
• Leap years where February has 29 days

I was thinking about creating calculated table with dates from actual months but I am unable to calculate the average to consider all conditions.

Any idea how to calculate this in Power BI? Any help is much appreciated!

Thank you
IvanS

[v-yanjiang-msft · Accepted solution]

Hi @IvanS ,

According to your description, I create a sample.

Here's my solution, create three columns.

M1 =
VAR _d =
    DATEADD ( 'Table'[Invoice Date], -1, MONTH )
RETURN
    IF ( DAY ( _d ) = DAY ( [Invoice Date] ), _d, BLANK () )

M2 =
VAR _d =
    DATEADD ( 'Table'[Invoice Date], -2, MONTH )
RETURN
    IF ( DAY ( _d ) = DAY ( [Invoice Date] ), _d, BLANK () )

M3 =
VAR _d =
    DATEADD ( 'Table'[Invoice Date], -3, MONTH )
RETURN
    IF ( DAY ( _d ) = DAY ( [Invoice Date] ), _d, BLANK () )

Then create a measure:

Average =
AVERAGEX (
    FILTER (
        ALL ( 'Table' ),
        'Table'[Invoice Date] IN { MAX ( [M1] ), MAX ( [M2] ), MAX ( [M3] ) }
    ),
    [Invoice Amount]
)

Get the result:

I attach my sample below for your reference.

Best regards,

Community Support Team_yanjiang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@IvanS ,

a new measure  =

var _max = max('Date', [Date])

Var _d1 = date(year(_max), month(_max)-1, day(_max))

Var _d2 = date(year(_max), month(_max)-2, day(_max))

Var _d3 = date(year(_max), month(_max)-3, day(_max))

return

averagex(Values(Date[Date]), calculate(sum(Table[Value]), filter(Date, Date[Date] in {_1d,_d2, _d3}) )

or

averagex(Values(Date[Date]), calculate(sum(Table[Value]), filter(Date, Date[Date] = _1d  || Date[Date] = _d2 ||  Date[Date] = _d3}) )

[IvanS]

Also, I am thinking if this formula will work between year change. There is definition only for YEAR(MAX(Date)) and therefore year change is not cosidered.

[IvanS]

Hi @amitchandak ,

I replicated formula but it is behaving kind of strange. It is showing average for the 1st of May but only if the whole month is selected (or using Month in Date Hierarchy). If I select specific day within May or select current month, the formula is returning (blank) value. 

3-Month Average Invoice Amount USD = 

VAR _maxdate = MAX('Date table'[Date])
VAR _month1 = DATE(YEAR(_maxdate), MONTH(_maxdate)-1, DAY(_maxdate))
VAR _month2 = DATE(YEAR(_maxdate), MONTH(_maxdate)-2, DAY(_maxdate))
VAR _month3 = DATE(YEAR(_maxdate), MONTH(_maxdate)-3, DAY(_maxdate))
RETURN
AVERAGEX(VALUES('Date table'[Date]), CALCULATE(SUM(BillingData[INVOICE_AMT_USD]), FILTER('Date table', 'Date table'[Date] in {_month1 , _month2, _month3} )))

Please check below screenshots: