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:   OrderNo OrderDate Parcels EstDi...
Need Help
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...