Forum Discussion

Surendra_thota's avatar
Surendra_thota
Helper II
9 years ago

DateDiff Days giving incorrect result

Hi All   we need to calculate number days between  [next reading due date]  and Report genertion date  (  Instead of Today () , we are taking a measure from Date dimenion to get max input date from...
Need Help
Tips and Tricks
Tutorial Requests
v-jiascu-msft's avatar
v-jiascu-msft
Microsoft Employee
9 years ago

Surendra_thota

 

Hi,

 

Could you provide a little sample please? It's possible in your situation. Consider this: there should be one column which would be the primary column ( making the row unique). So there is only one [Next Reading Due By]. We can create a MEASURE like this:

 

Days =
IF (
    MIN ( view_compliance[next reading due by] )
        < MAX ( 'Report Generation Date Table'[ReportGeneration] ),
    DATEDIFF (
        MIN ( view_compliance[Next Reading Due by] ),
        MAX ( 'Report Generation Date Table'[ReportGeneration] ),
        DAY
    ),
    0
)

The report would like this with a slicer of [ReportGeneration]:

 

TheUniqueColumn   [Next Reading Due by]   Days

 

Please have a try.

Best Regards!

Dale

  • Surendra_thota's avatar
    Surendra_thota
    Helper II
    9 years ago

    Hi Dale 

     

    I have tried as you said , atleast now we are not getting any issue but not giving correct answer ,  please see the attached screenshot , i took the report generation date as 1st jan 2017 ,  

    • v-jiascu-msft's avatar
      v-jiascu-msft
      Microsoft Employee
      9 years ago

      Surendra_thota

       

      Hi,

       

      The result is correct in some way. It seems that you used the formula as a calculated column. So "MAX ( 'Report Generation Date Table'[ReportGeneration] )" is always the same. It's "2018-12-31" in your table. You have to use it as a measure if you want it to respond to the slicer. Finally, create a report with the measure. Please have a try. 

       

      Best Regards!

      Dale