Monthly View from Weekly Data

Anonymous — Thu, 11 Jul 2019 12:18:22 GMT

Hi Everyone,

I've got stuck with a problem in converting from weekly data view to monthly.

I have a file that when on excel will work due to cell reference possibilities but not sure how to manage this on PowerBI.

The Formula weekly would be

([Stock for this week]/(([Sales for this week]+[Sales for Week+1])/2))*7

I created this by this measure in PowerBI

Week Calc. DOI = sum(Data[Stock])/

(sum(Data[Deliveries])+CALCULATE(sum(Data[Deliveries]),filter(all(Data[Week #]),Data[Week #]=(SELECTEDVALUE(Data[Week #])+1)))/2)*7

The Formula for the Monthly view is similar but would be

([Starting Stock for Month]/(([Total Sales for this Month]+[Total Sales for Month+1])/2))*30

The Issue I run into with this formula is that I only need the Stock from the 1st Week of the month, not the sum of all stock as its always weekly starting stock which adds onto each other. The total Sales for this Month and Month + 1 can be a sum of thie first row and the next row.

Can anyone help and give me advice on how to tackle this?

EDIT - Find the Data and PBIX file here

topic Monthly View from Weekly Data in Developer

Monthly View from Weekly Data