DAX help - Issues with Dateadd

#1

Hi Team,

Hope you are well.

I have created 2 simple measures

  • Open Jobs ( Calculates all the jobs that are open)
  • Open Jobs 5 days old (Calculates all the jobs that have been open for 5 or more days)

Issue: When I use the Open Jobs 5 days old against the user context to see how many of these each user have, I get an error like before. I looked up online and can’t seem to find a way around. Is there a correct way of doing this?

image
image.png1910×553 62.6 KB

I have attached a sampleDateAdd Help.pbix (164.7 KB) pbix file that replicates the scenario.

Appreciate all your help.

Many Thanks
S

#2

Hi @Suraaj,

To utilize time intelligence functions like DATEADD it’s better to use the date table. I see that you have one in your model. The ‘TimeSheet’[Date entered] column has gaps and duplicates in it so I guess the DATEADD -5 days is getting confused. To get the amount of open tickets that are X days open I use a calculated column myself. I have made one in your scenario and you can find it in the PBIX-file I have added. There may be cleaner ways (with a measure instead of a calculated column :wink: ) but this is how I do it.

Open jobs 5 plus days =
VAR entered = Sheet[Date Entered]
VAR openjobs = [Open Jobs]
VAR dur =
    CALCULATE (
        COUNT ( Dates[Date] );
        ALL ( Dates );
        DATESBETWEEN ( Dates[Date]; entered; TODAY () )
    )
RETURN
    IF ( dur >= 5; openjobs )

Hope it helps
Daniel

DateAdd Help.pbix (185.3 KB)

1 Like
#3

Hi @uriah1977,

Thank you so much for that. Yes, this will certainly help and it is clever how you have calculated this :clap:
Yep, would be keen to see if there are any other ways using measure.

Kudos again for your help!

Cheers
Suraaj