Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
Hello
I am running two columns which cacluate days between dates and both are throwing incorrect results
Time to Close RFIs = IF(ISBLANK(RFIs[ANSWERED_DATE]),BLANK(),(RFIs[ANSWERED_DATE]-RFIs[SUBMITTED_DATE])*1.)
Lapse Days=Time to Close RFIs = IF([ANSWERED_DATE]> [Required Date],[ANSWERED_DATE]-[Required_DATE])*1.0,Blank())
Solved! Go to Solution.
Recreated your table and created two different calculated columns with the same results
datediff = IF ( DATEDIFF ( 'Table2'[RequiredDate], Table2[AnswerDate], DAY ) > 0, -1 * ( DATEDIFF ( 'Table2'[RequiredDate], Table2[AnswerDate], DAY ) ), DATEDIFF ( 'Table2'[RequiredDate], Table2[AnswerDate], DAY ) )
where
Another Calculated column
datediff 2 = IF ( Table2[RequiredDate] < Table2[AnswerDate], -1 * DATEDIFF ( 'Table2'[RequiredDate], Table2[AnswerDate], DAY ), DATEDIFF ( 'Table2'[RequiredDate], Table2[AnswerDate], DAY ) )
These give the same result
Have you tried using the DATEDIFF function instead?
I tried but doesnt help
Time to Resolve = SWITCH(TRUE(),RFIs[PMSCRFI_REQUIRED_DATE]<RFIs[PMSCRFI_ANSWERED_DATE],-1*DATEDIFF(RFIs[PMSCRFI_REQUIRED_DATE],RFIs[PMSCRFI_ANSWERED_DATE],DAY),RFIs[PMSCRFI_REQUIRED_DATE]>RFIs[PMSCRFI_ANSWERED_DATE],DATEDIFF(RFIs[PMSCRFI_ANSWERED_DATE],RFIs[PMSCRFI_REQUIRED_DATE],DAY),0)
Recreated your table and created two different calculated columns with the same results
datediff = IF ( DATEDIFF ( 'Table2'[RequiredDate], Table2[AnswerDate], DAY ) > 0, -1 * ( DATEDIFF ( 'Table2'[RequiredDate], Table2[AnswerDate], DAY ) ), DATEDIFF ( 'Table2'[RequiredDate], Table2[AnswerDate], DAY ) )
where
Another Calculated column
datediff 2 = IF ( Table2[RequiredDate] < Table2[AnswerDate], -1 * DATEDIFF ( 'Table2'[RequiredDate], Table2[AnswerDate], DAY ), DATEDIFF ( 'Table2'[RequiredDate], Table2[AnswerDate], DAY ) )
These give the same result
Thanks everyone for stepping in
Both your answers were correct
Yes my filter context in the table was incorrect which was giving inconsistent results
The forumlas provided below work great too
The formula does look like it produces the correct answer sometimes, but not all the time.
In your table, are you summarizing your Date fields in any way? For example are you showing "First" or "Last" date for any reason? I'm wondering if your data is in such a way that your formula is calculating using a different context to how your matrix is displaying the rows.
Can you share some sample data, what result you are getting and what result you are expecting?
User | Count |
---|---|
85 | |
79 | |
64 | |
52 | |
46 |
User | Count |
---|---|
101 | |
44 | |
41 | |
39 | |
36 |