Forum Discussion

Chaucer's avatar
Chaucer
Helper II
4 years ago

Measure Help

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:

 

OrderNoOrderDateParcelsEstDispatchDateActualDispatchDate
120/02/2022120/02/202220/02/2022
220/02/2022220/02/202221/02/2022
320/02/2022320/02/202222/02/2022
420/02/2022221/02/202222/02/2022
521/02/2022121/02/202222/02/2022
621/02/2022221/02/202222/02/2022
721/02/2022321/02/202223/02/2022
821/02/2022222/02/202223/02/2022
922/02/2022122/02/202223/02/2022
1022/02/2022222/02/202223/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.

Need Help

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    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-5c3243d1f9

     

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

     

    • Chaucer's avatar
      Chaucer
      Helper II
      4 years ago

      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...
  • Chaucer's avatar
    Chaucer
    Helper II
    4 years ago

    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's avatar
      v-janeyg-msft
      Community Support
      4 years ago

      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.