DAX spread quantity over 5 days

1
votes

I have a table with columns Date, Type, and Qty.

Every time I see the type 'Start', I want to spread that quantity over the next 5 days (including the Start day). Note, the Start day may not always be the 6th day as in the example.

enter image description here

1
powerbidax
Are the dates always continuous or could there be a gap i.e. no row representing one of the five days to spread the values over? – BarneyL
@BarneyL The dates are always continuous. – oilers128
Is there only ever one row per day? – BarneyL

1 Answers

1
votes

I'd go for three measures something like this (syntax may be a little off as I'm writing without a platform to test on).

The first adds the normal records only.

SumNormal := CALCULATE(SUM(Qty),mytable[Type] = "Normal"

The second looks for the starts in the last 5 days and then divides by five.

SumStart := 
  VAR CurrentDate = mytable[Date]
  VAR StartDate = DATEADD([CurrentDate],-4,day)
CALCULATE(SUM(Qty)
   ,mytable[Type] = "Normal"
   ,FILTER(mytable,[Date]>= StartDate && [Date] <= CurrentDate)
)/5

Finally Add the two:

SumQty := [SumNormal] + [SumStart]

The above covers the basics but is relatively simple, it will only really work running at day level. Handling a total where some but not all of the five days the start value is spread over gets a lot more complicated.