Measure Help

[Chaucer]

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.

[Chaucer]

I tried messing about with Filters using an EARLIER, but that didn't work either....

TestEst =

CALCULATE(

sum('Order Lines'[Parcels]),

Filter(ALL('Order Lines'),

COUNTROWS(Filter('Order Lines', 'Order Lines'[EstDispatchDate] = EARLIER('Order Lines'[Order Date])

)

)))

[v-janeyg-msft]

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.

[amitchandak]

@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...

https://radacad.com/userelationship-or-role-playing-dimension-dealing-with-inactive-relationships-in...

[Chaucer]

@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 ...

EstDispatches = CALCULATE(sum('Order Lines'[Parcels]), Filter('Order Lines', 'Order Lines'[EstDispatchDate] = 'Order Lines'[Order Date]))

The above measure nearly does, it, but just sums the parcels where EstDispatchDate=OrderDate...