Date Diff Between Start Date of Date Slicer and Transaction Date in Sales Table

[dscott73]

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])
RETURN
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.

[v-rongtiep-msft]

Hi @dscott73 ,

Please have a try.

Create a measure based on the Days Since. 

Measure = var _b=SUMMARIZE(dim.Date,dim.Date[Date],"aaa",[Days Since])
return
IF(HASONEVALUE(dim.Date[Date]),dim.Date[Days Since],SUMX(_b,[aaa]))

I have also found a similar post, please refer to it to see it it helps you.

Total value in the table/ card does not tally 

If I have misunderstood your meaning, please provide your pbix file without privacy information and desired output.

Best Regards
Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[dscott73]

So it seems, when dim.Date[ActualDate] is both used in the slicer, AND in the table, the are some issues with filter context.

If I move the actual TransactionDate onto the fact.SalesTable along with its DIM_KEY, and use the date field from fact.SalesTable in the grid rather than from dim.Date, the values are shown correctly.

That begs the question, is there a way to use both? Use a disconnected dim.Date for the slicer, and use that for the date calculations?