Back dated calculation by week. 12 weeks -(minus) 24 weeks rolling with respect to change year.

[Anonymous]

I'm trying to calculate retaintion % by week. 

Challegne I'm facing: When years change, formula is not taking in account back dated week while calculating. 12 week - 24 weeks.
In below image when year is 2018 week 2 show 100%. Ideally it go back 12 week and 24 weeks from week 2(2018) for calculation. So, From week 43 (2017) to week 2 (2018) and same for  24 weeks, week 31(2017) to week2(2018).

Formulas:

1. Draw on graph: Retain % = 1-DIVIDE([Total1],[Total Final OutPut],0)

2. Total1=(
CALCULATE([ConsigneeCount],
FILTER(ALL('Dummy'[Week]),'Dummy'[Week]<=MAX('Dummy'[Week])))
-
CALCULATE([ConsigneeCount],
FILTER(ALL('Dummy'[Week]),
'Dummy'[Week]>=MAX('Dummy'[Week])-12 &&
'Dummy'[Week]<=MAX('Dummy'[Week]) )))
-
(
CALCULATE([ConsigneeCount],
FILTER(ALL('Dummy'[Week]),'Dummy'[Week]<=MAX('Dummy'[Week])))
-
CALCULATE([ConsigneeCount],
FILTER(ALL('Dummy'[Week]),'Dummy'[Week]>=MAX('Dummy'[Week])-24 &&
'Dummy'[Week]<=MAX('Dummy'[Week]))
))
 

3. Total Final OutPut = IF( NOT ISFILTERED('Dummy'[Week]),
CALCULATE(
COUNTAX(
FILTER(VALUES('Dummy'[Consignee] ),[Consignee MCount]),[Consignee MCount])),
CALCULATE(
COUNTAX(
FILTER(VALUES('Dummy'[Consignee] ),[Consignee MCount] >1),[Consignee MCount]),
FILTER(ALL('Dummy'[Week]),
'Dummy'[Week]<=MAX('Dummy'[Week])
)
))

4. Consignee  MCount = COUNTA('Dummy'[Consignee])

[v-yulgu-msft]

Hi @Anonymous,

Would you please share some sample data to help us better understand above formulas?

Regards,

Yuliana Gu

[Anonymous]

Hi @v-yulgu-msft

Thanks for reply.

uploaded sample .

https://nofile.io/f/rrxXFYGJKRs/New+Challenge1.pbix

[v-yulgu-msft · Accepted solution]

Hi @Anonymous,

I would suggest you add an index column to sort the order of weeks per year.

In query editor mode, add a custom column.

WeekNum=[Year]*100+[Week of Year]

Then, add an index column.

Instead of referring to the [Week] column in each measure, please replace it with the [Index] column. 

For example:

Total1=(
CALCULATE([ConsigneeCount],
FILTER(ALL('Dummy'[Index]),'Dummy'[Index]<=MAX('Dummy'[Index])))
-
CALCULATE([ConsigneeCount],
FILTER(ALL('Dummy'[Index]),
'Dummy'[Index]>=MAX('Dummy'[Index])-12 &&
'Dummy'[Index]<=MAX('Dummy'[Index]) )))
-
(
CALCULATE([ConsigneeCount],
FILTER(ALL('Dummy'[Index]),'Dummy'[Index]<=MAX('Dummy'[Index)))
-
CALCULATE([ConsigneeCount],
FILTER(ALL('Dummy'[Index]),'Dummy'[Index]>=MAX('Dummy'[Index])-24 &&
'Dummy'[Index]<=MAX('Dummy'[Index]))
))

Regards,

Yuliana Gu

[Anonymous]

@v-yulgu-msft

 Thanks for your time and helping me out.

that really work , Thums up..