DateDiff Days giving incorrect result

[Surendra_thota]

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 filter  which is constant across all rows), i am attaching the screen shot , please let us know where we are going wrong .

[v-jiascu-msft]

@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]

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]

@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

[Anonymous]

That is super weird, and I am mostly commenting just so I can follow along 😄

My only thought would be to try to wrap CALCULATE( )  around your call to DATEDIFF.

[Surendra_thota]

Hi Scottsen

when i use today() function i am getting correct value 

Days Due  = View_Compliance[Next Reading Due By]- Today()

but when i want use a constant date ie selected from input slicer , it is nota accepting , even i tried to give max reportiondate as measure  as well directly as shown below but bothcases it is taking only last value of the genetation date table value but not slected value.

Days Due Column = View_Compliance[Next Reading Due By]- MAX('Report Generation Date Table'[Report Generation Date])

How to pass a slected date value from slicer used in difffrent table as variable

[Anonymous]

* I'm curious what the relationship is between View_Compliance and 'Report Generation Date Table' tables.   I didn't catch before these were from different tables, and you might just need to use the RELATED( ) function for 1 of your columns.

* You mention a "slicer" -- note those will NOT impact a calculated column in any way.  Only measures are impacted by slicers.  Likely you can write a "Total Difference" measure that aggregates the difference from each line.

[Surendra_thota]

Hi Scottsen.
There is no relation between those 2 tables , I just took report generation table to get date input from user ie linked slicer , then I am creating measure by taking that date. I want to use that measure in view_compliance to due dates . Is there any other way to achieve user input date to calculate datediff on each row

[v-jiascu-msft]

@Surendra_thota

Hi,

I guess you use Difference as a calculated column, not a measure. As @Anonymous said, a calculated column can’t answer the slicer. So you should make some change to the formula and make it as a measure. Please try it.

Difference =

SWITCH (

    TRUE (),

    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

)

Best Regards!

Dale

[Surendra_thota]

HI Dale 

we need to  get the duedate ie datediff on everyrow , for each row  [next reading due date ] column - userinputdate ( which is same for all rows,  this is  instaed of today()  we are taking some userinpu date , so they want to see that relvant date based on that date).

we are taking ReportGeneration as measure where we  are taking max of user input date from slicer.

The issue  is Reportgeneration date table and actual fact table are not  linked and  reportgenration date always taking last date of datedimension. how to use non related measure  in different table  on row by row basis

[Venkatdevu]

@Surendrayou need to use lastdate() function in place of max() function as max takes scalar value its not giving date diff

[Surendra_thota]

Hi Venkat

I have user that last date function also , still San result that is that date of date dimension is coming , some how the selected column is not reflecting in 2 nd table but if we use the same measure in card it is giving selected date, we need to know how use selected date in different table for calculated column

[v-jiascu-msft]

@Surendra_thota

Hi, 

Do you mean you want to use slicer to impact the calculated column? There is no way to do that. You have to use a measure and  create a report.

Best Regards!

Dale

[Surendra_thota]

Hi Dale 

Yes , i am using user input date slicer to calculate datediff in calculated column , is it not possible ?  we are able to get a measure by taking max date from selected date and then when i am using that in another table datediff calculation it is not giving expected results , the measure value is taking max value of the date dimension used for slicer instaed of slected date .

[v-jiascu-msft]

@Surendra_thota

Hi,

Yes, that's impossible by far. A calculated column is one part of a table. A slicer works in the report. If the slicer can change the value of part of a table, that would be a big mess. Maybe there will be a solution in the future. 

I can't reproduce your scenario. Could you please post your formulas and a picture of the wrong result here?

Best Regards!

Dale

[Surendra_thota]

Hi Dale 

My use case is simple we are genereating a report whihc contains calculated column called due dates , that duedates column is calculated  

Days Due  = View_Compliance[Next Reading Due By]-  Today()

So the Days Due calculated column give  number days either +/1  with reference to Today()

User asked to  to put a paarmeter instaed of Today()   so that they can give refernce date instead of today .

what we planned  here to achieve this is we are taking   seperate datetime table where the user can  select data and we are  creating measure to take the selectdate and we want to use that measure in Days due calculated column .

Problem here is we are getting result as the refernce date as max value in that new date dimension instaed os what user is selected .

[Anonymous]

Agree, calculated column is just NOT going to help you.  And while it's possible to write the DAX for this in a measure (which is what you should do), I get the feeling that your current data model will make that needlessly complex.  I say that based on your statement that the 2 tables in question are not related.

In general, you are going to be looking to use SUMX to interate over the rows.  Something like:

Selected Date = MAX(Calendar[Date])

Total Days Due  = SUMX(View_Compliance, View_Compliance[Next Reading Due By] - [Selected Date])