cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper III
Helper III

Dynamic Dates between dax function

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
 
 
3 ACCEPTED SOLUTIONS
Super User III
Super User III

Replace 2019 with YEAR(TODAY()) - 1

 

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



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

Community Support
Community Support

Hi @reynags91 ,

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

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

View solution in original post

Super User IV
Super User IV

@reynags91 , 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...

 

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

3 REPLIES 3
Super User IV
Super User IV

@reynags91 , 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...

 

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

Community Support
Community Support

Hi @reynags91 ,

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

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

View solution in original post

Super User III
Super User III

Replace 2019 with YEAR(TODAY()) - 1

 

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



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

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors