Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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?
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
73 | |
72 | |
37 | |
31 | |
26 |
User | Count |
---|---|
95 | |
50 | |
43 | |
40 | |
35 |