Forum Discussion
DAX for negative date value
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+")
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
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.
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+")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