Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills 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

PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors