I have a star schema. fact.SalesTable with the sales transaction info (Item, TransactionDate, Qty, Amount etc...)
There are the associated dimensions. Dim.Date and Dim.Item.
The tables are connected via 1:* relationships as follows: Dim.Date.DateDIM_KEY = Fact.SalesTable.TransDateDIM_KEY and Dim.Item.ItemDIM_KEY = Fact.SalesTable.ItemDIM_KEY
I have a Between Date Slicer that uses dim.Date.ActualDate
I am trying to calculate the days between the selected Start Date of the Date Slicer, and the associated Transaction Date on fact.SalesTable
I though this would be as simple as:
The below is a measure to be clear and not a calculated column.
Days Since =
var StartDate = CALCULATE(MIN(dim.Date[ActualDate]), ALLSELECTED(dim.Date[ActualDate]))
var TransDate = MAX(dim.Date[ActualDate])
DATEDIFF(StartDate, TransDate, DAY)
When I put dim.Item[ItemNumber], dim.Date[ActualDate] and [Days Since] into a grid. The values are incorrect. The Days Since measure shows zero or the incorrect value at the line level.