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
Kgathright
Helper II
Helper II

DAX for negative date value

I have two measures that we are currenlty using for date ranges. We have a scenario that was missed, where an end date is earlier than the start date. I've tried a couple of things already. Just need some suggestions. Thanks! 


NameOfMeasure = SWITCH(TRUE(),AND ([NameOfMeasure]>=0,[NameOfMeasure]<=30),"0-30",
AND ([NameOfMeasure]>=31,[NameOfMeasure]<=60),"31-60",
AND ([NameOfMeasure]>=61,[NameOfMeasure]<=90),"61-90",
AND ([NameOfMeasure]>=91,[NameOfMeasure]<=120),"91-120","121+")


NameOfMeasureRank = SWITCH('Table'[Column],"0-30",1,"31-60",2,"61-90",3,"91-120",4,"121+",5)

1 ACCEPTED SOLUTION

this post was helpful, but I didn't use the proposed solution because I ended up altering the existing Switch statement when I realized the last line was just a catch all for all that was left: 

 

From: 

AND ([NameOfMeasure]>=91,[NameOfMeasure]<=120),"91-120","121+")

 

To: 

,AND ([NameOfMeasure]>=91,[NameOfMeasure]<=120),"91-120"
,AND ([NameOfMeasure]>=121,[NameOfMeasure]<=1000000),"121+")

View solution in original post

6 REPLIES 6
Kgathright
Helper II
Helper II

Sorry those are columns NOT measures! 

Hi @Kgathright , what's your expected result with these columns? I cannot understand what you want to get. Can you provide some sample data and your desired result?

In the graph this data is using, we are calculating how long someone was on an assignment in days: 

0-30 days

31-60 days

61-90 days

91-120 days

 

So there is a total count for each cycle for date parameters on the report. What I need to do is prevent the records that have a negative value to be counted in this graph. Users that had an assignment that eneded before it even started. 

Example:
Start Date = 1/15/2020

End Date = 1/12/2020

 

 

@Kgathright,

10091.jpg

if you want to count days which don’t include negative days value, you can create a measure like below. It has filtered out negative values before calculate the sum total

 

Days without negative value = CALCULATE(SUM(Table1[Days]),Table1[Days] >= 0)

 

You will see the difference below.

10092.jpg

You can also remove the negative values in a column if you like.

10093.jpg

Best Regards,

Community Support Team _ Jing Zhang

If this post helps, please consider Accept it as the solution to help other members find it.

this post was helpful, but I didn't use the proposed solution because I ended up altering the existing Switch statement when I realized the last line was just a catch all for all that was left: 

 

From: 

AND ([NameOfMeasure]>=91,[NameOfMeasure]<=120),"91-120","121+")

 

To: 

,AND ([NameOfMeasure]>=91,[NameOfMeasure]<=120),"91-120"
,AND ([NameOfMeasure]>=121,[NameOfMeasure]<=1000000),"121+")

@Kgathright Glad to see that you have found the solution. You may accept the appropriate post as the solution to close this thread and help others find it easily. Thanks a lot.

 

Best regards,

Jing

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.