Showing results for 
Search instead for 
Did you mean: 
Resolver I
Resolver I

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

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.

Community Support
Community Support

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

Resolver I
Resolver I

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?

Helpful resources

Vote for T-Shirt Design

Power BI T-Shirt Design Challenge 2023

Vote for your favorite t-shirt design now through March 28.

March 2023 Update3

Power BI March 2023 Update

Find out more about the March 2023 update.

March Events 2023A

March 2023 Events

Find out more about the online and in person events happening in March!

Top Solution Authors