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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.