cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dscott73
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])
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.

2 REPLIES 2
v-rongtiep-msft
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])
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
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

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

MPPC 2023 PBI Carousel

Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors