Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi,
I'm trying to create a couple of very basic measures, and for some reason the logic is escaping me.
I've got a very simple data table as follows:
OrderNo | OrderDate | Parcels | EstDispatchDate | ActualDispatchDate |
1 | 20/02/2022 | 1 | 20/02/2022 | 20/02/2022 |
2 | 20/02/2022 | 2 | 20/02/2022 | 21/02/2022 |
3 | 20/02/2022 | 3 | 20/02/2022 | 22/02/2022 |
4 | 20/02/2022 | 2 | 21/02/2022 | 22/02/2022 |
5 | 21/02/2022 | 1 | 21/02/2022 | 22/02/2022 |
6 | 21/02/2022 | 2 | 21/02/2022 | 22/02/2022 |
7 | 21/02/2022 | 3 | 21/02/2022 | 23/02/2022 |
8 | 21/02/2022 | 2 | 22/02/2022 | 23/02/2022 |
9 | 22/02/2022 | 1 | 22/02/2022 | 23/02/2022 |
10 | 22/02/2022 | 2 | 22/02/2022 | 23/02/2022 |
No date table, no other tables.
What I want to do is knock up a graph that uses the order date as its X-axis, and plots the following on the Y.
Total No of parcels ordered by day.
Total No of parcels that should have dispatched on that date.*
Total No of parcels that actually dispatched on that date.*
Difference between est and actual.
Trailing 7 day averages of each.
It's the *'ed one's I 'm struggling to do.
Any help gladly received.
I tried messing about with Filters using an EARLIER, but that didn't work either....
Hi, @Chaucer
These can be expressed by measure.
If you use measure, change 'EARLIER' to 'selectedvalue'.
Like:
Total No of parcels ordered by day =
COUNTX (
FILTER ( ALL ( table ), [OrderDate] = SELECTEDVALUE ( table[OrderDate] ) ),
[OrderNo]
)
But I don't know how you're going to put the two on a line chart and the logic of ’Trailing 7 day averages of each.‘
Can you explain it?
Best Regards,
Community Support Team _Janey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Chaucer , One of the options is to create a date table and join all dates with that. One join will be active and the rest will be inactive, which you can use using userelationship
Last 7 days
Rolling 12 Sales =
var _max = maxx(allselcted(date),date[date]) // or today()
var _min = _max -7
return
CALCULATE(countrows(Table) ,filter(date, date[date] <=_max && date[date] >=_min))
Today =
CALCULATE(countrows(Table) ,filter(date, date[date] =today()) )
Yesterday=
CALCULATE(countrows(Table) ,filter(date, date[date] =today()) )
Day Intelligence - Last day, last non continous day
https://medium.com/@amitchandak.1978/power-bi-day-intelligence-questions-time-intelligence-5-5-5c324...
@amitchandak - is there any way of doing it without a date table? It's a Direct Query...
If it was excel, I would just use a sumif, ie. for EstDispatch, sum the number of parcels where EstDispatchDate=OrderDate ...
User | Count |
---|---|
77 | |
74 | |
60 | |
60 | |
46 |
User | Count |
---|---|
109 | |
95 | |
86 | |
79 | |
61 |