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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
JimJim
Helper V
Helper V

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
Super User
Super User

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
Super User
Super User

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

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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