cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Shelley Member
Member

DATESBETWEEN is Running Past Current Date

I'm hoping I'm just missing something simple. I'm struggling to figure out how to do what I want.

 

I am trying to calculate Machine Downtime within a 90 Day rolling window. In other words, I want to calculate the total Machine Downtime in the last 90 days, every day. I think I have the rolling part working, but it is giving data for dates beyond today, which probably has something to do with our date calendar, spanning many years before today and beyond today.  I was trying to use NOW() or TODAY() for the start date, but I must be trying to use those functions improperly.  Here's what I have:

 

Test 90-Days Rolling Downtime =
CALCULATE([Total Downtime (Secs)],
   DATESBETWEEN('Daily Calendar'[Date],
       NEXTDAY(DATEADD(LASTDATE('Daily Calendar'[Date]),-90, DAY)),
           LASTDATE('Daily Calendar'[Date])))

 

Your expertise is greatly appreciated. Thanks!

1 REPLY 1
Super User
Super User

Re: DATESBETWEEN is Running Past Current Date

Hey,

 

maybe it would be helpful to use

LASTNONBLANK('Daily Calendar'[Date], SUM('FactTable'[column]))

this would return the last date from your facttable

 

Hope this may help

Hamburg - Germany
If I answer your question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 491 members 4,614 guests
Please welcome our newest community members: