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
irnm8dn
Post Prodigy
Post Prodigy

Assign Value to Calculation in DATEDIFF and Use Case Handling

I am currently using the following DAX statement to calculate the difference between two dates.

 

Day Count of Drop = SWITCH(
TRUE(),
'KPI Dashboard AD'[Campaign Creation Date]<'KPI Dashboard AD'[Start Date], DATEDIFF('KPI Dashboard AD'[Campaign Creation Date],'KPI Dashboard AD'[Start Date],DAY),
'KPI Dashboard AD'[Start Date]>'KPI Dashboard AD'[Campaign Creation Date], DATEDIFF('KPI Dashboard AD'[Start Date],[Days Between Drop Creation and Start],DAY)*-1,
0
)

 

This seems to work, but there are two use cases that this statement doesn't account for:

 

  1. When Start Date is greater than the Creation Date - I would like this to show "Error"
  2. When Start Date is the Same as Creation Date - I would like this to show "0"

Once the above use cases have been handled, I am looking to have a process that assigns the value to the calculation.

 

When "Error" show "Error" (Use Case #1 above)

When calc is "0" Very Urgent (Use Case #2 above)

When calc between "1" and "3" Urgent

When calc between "4" and "7" Standard
When calc is "8" or greater, Not Urgent

 

Appreciate the feedback on how to do this.

 

4 REPLIES 4
Phil_Seamark
Employee
Employee

HI @irnm8dn

 

Is this calculated column close?

Day Count of Drop = 
VAR MyDateDiff = INT('KPI Dashboard AD'[Start Date]) - INT('KPI Dashboard AD'[Campaign Creation Date]) RETURN SWITCH( True(), MyDateDiff < 0 , "Error", MyDateDiff = 0 , "Very Urgent" , MyDateDiff < 4 , "Urgent" , MyDateDiff < 8 , "Standard" , "Not Urgent" )

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

@Phil_Seamark

 

Phil, this is awesome.

 

Can you help adjust my original statement so that the following works as a proper calc?

 

When Start Date = Create Date "0"
When Start Date is greater than Create Date = "Error"

 

Essentially, I will have tow columns in my table

 

1.  The count of days between

2.  The  value which you addressed in your original post

 

Thanks!

HI @irnm8dn

 

So just to clarify, you are after a column that shows the difference between your two dates in days - but if the value is negative you want to display "Error"?

 

That would mean mixing data types, so all your numbers would be text.  

 

Have I understood correctly, or perhaps you could post a mock up (using excel) of what you would like the column to look like.

 

I'm just not sure what you mean by "proper calc"


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Thanks for the clarification Phil.  You understand it correctly, and I see the conflict in what I have asked for.

 

I would take the negative value with a calc.  My statement does not address this condition.

 

That way I could differentiate between negative and true "0".

 

Sceondly, if you could help me understand your initial solution (which works great), that would help this newbie to not ask again.  Teach a man to fish, if you know what I mean.

 

Thanks again.

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.