Dynamic Dates between dax function

[Anonymous]

Hi Everyone,

Im trying to calculate the average values between two periods of dates. See below

CALCULATE(AVERAGE('Working Gas in Underground Storage (W)'[VALUE]),DATESBETWEEN('Working Gas in Underground Storage (W)'[DATE],date(2015,01,01),DATE(2019,12,31)))

However how can I make this dynamic?

I would like a 5 year rolling average but ignoring latest year. Currently 2020. ( We dont count it as we are currently in it) Any easy way to do this? As how I have it now i would have to come back and change the dates when 2021

Any help ideas are greatly appreciated it

[edhans · Accepted solution]

Replace 2019 with YEAR(TODAY()) - 1

That will move forward. So DATE(YEAR(TODAY()) - 1, 12, 31)

[v-yiruan-msft · Accepted solution]

Hi @Anonymous ，

You can update the formula of your measure or calculated column as below:

Measure =
CALCULATE (
    AVERAGE ( 'Working Gas in Underground Storage (W)'[VALUE] ),
    DATESBETWEEN (
        'Working Gas in Underground Storage (W)'[DATE],
        DATE ( Year(Today())-5, 01, 01 ),
        DATE ( Year(Today())-1, 12, 31 )
    )
)

Best Regards

Rena

[amitchandak · Accepted solution]

@Anonymous , Check

last year end date = date(year(today())-1,12,31)
start date 5 years back = date(year(today())-5,1,1)

refer

https://community.powerbi.com/t5/Community-Blog/Date-as-Dateadd-Decoding-Date-and-Calendar-5-5-Power-BI-Turning/ba-p/1187827

[v-yiruan-msft · Accepted solution]

Hi @Anonymous ，

You can update the formula of your measure or calculated column as below:

Measure =
CALCULATE (
    AVERAGE ( 'Working Gas in Underground Storage (W)'[VALUE] ),
    DATESBETWEEN (
        'Working Gas in Underground Storage (W)'[DATE],
        DATE ( Year(Today())-5, 01, 01 ),
        DATE ( Year(Today())-1, 12, 31 )
    )
)

Best Regards

Rena

[edhans · Accepted solution]

Replace 2019 with YEAR(TODAY()) - 1

That will move forward. So DATE(YEAR(TODAY()) - 1, 12, 31)