Skip to main content
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.


Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.