Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
JimJim
Responsive Resident
Responsive Resident

Calculate days between start date and end date (with a twist)

Hello,

 

I have a meaure that is using the divide function, it's taking two measures as input parameters (CompletedCount and NumberOfDays). Visuals using this measure are pretty slow and I suspect that the NumberOfDays measure is to blame.

 

What the NumberOfDays measure does is calculate the number of days between a start date and end date. If the end date > today then use today else use end date. here is the measure. Is there a more efficient way to write this?

 

 
Day Count = 
VAR StartDate =
    MIN ( 'Completed Date'[Date] )
VAR EndDate =
    IF (
        MAX ( 'Completed Date'[Date] ) > TODAY (),
        TODAY (),
        MAX ( 'Completed Date'[Date] )
    )
RETURN
    INT ( EndDate - StartDate )
 
1 ACCEPTED SOLUTION
AlB
Community Champion
Community Champion

Hi @JimJim 

I do not think it can get much more efficient than what you already have. I really doubt the issue is there. Maybe avoiding the double invocation of MAX ( 'Completed Date'[Date] ) like:

Day Count = 
VAR StartDate =
    MIN ( 'Completed Date'[Date] )
VAR EndDate = MAX(TODAY(), MAX ( 'Completed Date'[Date] ) )
RETURN INT ( EndDate - StartDate )

but the change, if any, will be minimal since the engine is quite efficient in calculating operations like MAX ( 'Completed Date'[Date] ).

Have you tried DAX Studio? It's  a very useful tool to pinpoint performance bottlenecks.

 

Cheers

View solution in original post

2 REPLIES 2
AlB
Community Champion
Community Champion

Hi @JimJim 

I do not think it can get much more efficient than what you already have. I really doubt the issue is there. Maybe avoiding the double invocation of MAX ( 'Completed Date'[Date] ) like:

Day Count = 
VAR StartDate =
    MIN ( 'Completed Date'[Date] )
VAR EndDate = MAX(TODAY(), MAX ( 'Completed Date'[Date] ) )
RETURN INT ( EndDate - StartDate )

but the change, if any, will be minimal since the engine is quite efficient in calculating operations like MAX ( 'Completed Date'[Date] ).

Have you tried DAX Studio? It's  a very useful tool to pinpoint performance bottlenecks.

 

Cheers

JimJim
Responsive Resident
Responsive Resident

Hi @AlB , 

 

Thanks for your reply, I didn't know that aggregate functions such as MAX could take multiple expressions so that is really good to know. However, I need to take the earliest date between CompletedDate and TODAY so the variable should be:

 

VAR EndDate = MIN(TODAY(), MAX ( 'Completed Date'[Date] ) )

Funnily enoough the visuals appear to be working fine just now (I have reopened the report to remove any caching and it's still running fast) so I don't think DAX studio would be much help at this moment. Maybe just one of these glitchy things, I will keep an eye on it.

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors