Forum Discussion
datediff for dates used in filters
I am struggling to find the correct dax expression for this. i have a date column which is used as a filter in the dashboard. then i have another column (deadline date) as part of the data. I wanted to create a column/measure that would give me the difference between whatever date is filtered and the deadline date.
so for example, i filtered to Nov 10, and project 1 is due on Nov 30, the measure/column would give me 20 days as a result.
the intention is to group the projects that are due in the next weeks/months using bucks/bins. any help?
Hi tropic,
Taking into acount that you are using a slicer to filter your dashboard you need to create a measure, or different metrics depending on the bin.
The basic calculation will be something similar to this:
Days difference = VAR _TempTable = ADDCOLUMNS ( Table, "_DaysDifference", DATEDIFF ( Table[DeadlineDates], MAX ( SlicerTable[DateColumn] ), DAY ) ) RETURN MAXX ( _TempTable, [_DaysDifference] )This will give you the value per each line of your table of the difference then you need to calculate the buckets, this can be done using a table with the values of the bins.
Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.
If the information is sensitive please share it trough private message.
1 Reply
- MFelix
Super User
Hi tropic,
Taking into acount that you are using a slicer to filter your dashboard you need to create a measure, or different metrics depending on the bin.
The basic calculation will be something similar to this:
Days difference = VAR _TempTable = ADDCOLUMNS ( Table, "_DaysDifference", DATEDIFF ( Table[DeadlineDates], MAX ( SlicerTable[DateColumn] ), DAY ) ) RETURN MAXX ( _TempTable, [_DaysDifference] )This will give you the value per each line of your table of the difference then you need to calculate the buckets, this can be done using a table with the values of the bins.
Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.
If the information is sensitive please share it trough private message.