Need to Calculate Week of Supplies

[mohan_g_das1986]

I have data by week, which is Inventory & Sales as below.. I dont have date just week in the below format.

now need to calculate WOS = Current week Inventory / 13 Average week Sales Out (Which should be rolling weeks)

can you guys help me?

[MitchM · Accepted solution]

I took a stab at this. The basis of my idea is using PowerQuery to convert the Week column to a usable number column. This can be done by replacing the W with '' (see the table in my pic). You can then use a few measures to get your values. I was not sure if you wanted the sales from the latest week in the average or the preceeding 13 weeks. Here are the measures:

13WeekAverage(CurrentIncluded) =
AVERAGEX(
TOPN(13,'Sample','Sample'[Week_Year]),
'Sample'[Sales]) 

13WeekAverage(CurrentNotIncluded) =
VAR LatestWeek =
CALCULATE(
MAX( 'Sample'[Week_Year] ),
ALL( 'Sample' )
)
RETURN
CALCULATE(
AVERAGE( 'Sample'[Sales] ),
TOPN(
13,
SUMMARIZECOLUMNS(
'Sample'[Week_Year], 'Sample'[Sales],
FILTER( 'Sample', 'Sample'[Week_Year] <> LatestWeek )
),
'Sample'[Week_Year]
)
)

Inventory_LatestWeek = 
    CALCULATE(
        SUM( 'Sample'[Inventory] ),
        FILTER('Sample', 'Sample'[Week_Year] = MAX( 'Sample'[Week_Year] ))
    )

[mohan_g_das1986]

Thanks for the calculating; but this will give me only static information;; you help me to create a rooling 13week information... & can you show the data in table by Week Vs WOS?

---

@MitchM wrote:

I took a stab at this. The basis of my idea is using PowerQuery to convert the Week column to a usable number column. This can be done by replacing the W with '' (see the table in my pic). You can then use a few measures to get your values. I was not sure if you wanted the sales from the latest week in the average or the preceeding 13 weeks. Here are the measures:

13WeekAverage(CurrentIncluded) =
AVERAGEX(
TOPN(13,'Sample','Sample'[Week_Year]),
'Sample'[Sales]) 

13WeekAverage(CurrentNotIncluded) =
VAR LatestWeek =
CALCULATE(
MAX( 'Sample'[Week_Year] ),
ALL( 'Sample' )
)
RETURN
CALCULATE(
AVERAGE( 'Sample'[Sales] ),
TOPN(
13,
SUMMARIZECOLUMNS(
'Sample'[Week_Year], 'Sample'[Sales],
FILTER( 'Sample', 'Sample'[Week_Year] <> LatestWeek )
),
'Sample'[Week_Year]
)
)

Inventory_LatestWeek = 
    CALCULATE(
        SUM( 'Sample'[Inventory] ),
        FILTER('Sample', 'Sample'[Week_Year] = MAX( 'Sample'[Week_Year] ))
    )

---