DAX for negative date value

[Anonymous]

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)

[Anonymous]

Sorry those are columns NOT measures! 

[v-jingzhang]

Hi @Anonymous , 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?

[Anonymous]

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

[v-jingzhang]

@Anonymous,

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.

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

Best Regards,

Community Support Team _ Jing Zhang

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

[Anonymous · 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+")

[v-jingzhang]

@Anonymous 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