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.
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.
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?
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!
User | Count |
---|---|
107 | |
74 | |
66 | |
50 | |
49 |
User | Count |
---|---|
163 | |
85 | |
76 | |
68 | |
67 |