Forum Discussion
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)
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+")
6 Replies
v-jingzhangCommunity Support
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?
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/2020End Date = 1/12/2020