Forum Discussion
Helper IIDateDiff Days giving incorrect result
* 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.
Helper IIThere 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-msftMicrosoft Employee
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
- Surendrayou need to use lastdate() function in place of max() function as max takes scalar value its not giving date diff
