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])

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

Not applicable

8 years ago

Solved

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

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])

Need Help

v-yulgu-msft

8 years ago

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

4 Replies

v-yulgu-msft
Microsoft Employee
8 years ago
Hi Anonymous,

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

Regards,

Yuliana Gu
- Anonymous
  Not applicable
  8 years ago
  Hi v-yulgu-msft
  
  Thanks for reply.
  
  uploaded sample .
  https://nofile.io/f/rrxXFYGJKRs/New+Challenge1.pbix
  - v-yulgu-msft
    Microsoft Employee
    8 years ago
    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

Forum Discussion

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

4 Replies

Related content

Rolling accumulation

Rolling accumulation

Rolling 12 Month calculation

Rolling 12 Column Monthly Calculation

Rolling Calculation