Cumulative Percentage Total By Fiscal Week

[romoguy15]

Hello Everyone,

I have been spending some time searching the forums to try and see if I can figure out a solution to give me a culutative percentage by fiscal week. In my calculation, I am unable to identify all the weeks to be able to divide my cumulative terms.

To give a bit of context, in order to get an annualized terms %, I have to take ( total terms / current week ) * 52weeksinyear / Avg Head Count. In my calculation below, I've tried and was able to get a cumulative total for the terms and my cumulative avg head count. The issue I am running into, is my VAR for Current week is only able to identify the actual current week. I would like to be able to divide the  [Terms Cumulative Total] week by week

I have uploaded a sample file for this

https://1drv.ms/u/s!AqID1H0nHPOzhBPUVCHQy_avV8-r?e=htQs8T

Cumulative Annualized Terms % =

VAR CurrentWeek=MAXX(FILTER(ALL('Calendar'),[Date]=TODAY()),[Fiscal Week] -1)

RETURN

CALCULATE(DIVIDE([Terms Cumulative Total], CurrentWeek) * DIVIDE(52, [SUM Of Average Head Count By DC Cumulative Total]))

[romoguy15 · Accepted solution]

I have solved my own issue. Switching my variable in the forumla using selectedvalue for the fiscal week worked.

VAR FiscalWeek = SELECTEDVALUE('Calendar'[Fiscal Week]) worked to evaluate by each week week number in the table.

[romoguy15]

I basically have to figure out how to sum the average by DC and by Fiscal Week

SUM Of The Average Head Count By DC = CALCULATE(SUMX(SUMMARIZE('Head Count', 'Head Count'[Cost Center], "Avg Head Count By DC", [Avg Head Count]), [Avg Head Count By DC]))

I don't know how to incororate the fiscal in there.

[romoguy15]

I figured out a portion of this formula. 

I changed my VAR week formula from 
VAR CurrentWeek=MAXX(FILTER(ALL('Calendar'),[Date]=TODAY()),[Fiscal Week] -1)

to using selectedvalue in the fiscal calendar to let it iterated by each week number like this

VAR FiscalWeek = SELECTEDVALUE('Calendar'[Fiscal Week])

The only other problem in my formula is my [SUM Of Average Head Count By DC Cumulative Total] calculation.

I removed that from the whole formula and isolated it with a fixed number and everything worked perfectly. I can't seem to figure out how to summarize this by DC and by Fiscal Week in my calendar.

[romoguy15]

Bump anyone?

[romoguy15]

Hi @v-easonf-msft , any thoughts?

[romoguy15]

Bumping this up. Any possible ideas on how to achieve this?

[v-easonf-msft]

Hi, @romoguy15 

Not very clear. Can you explain bit more?
Sample data and expected result will make it easier for us to understand and  solve your problem.

Best Regards,
Community Support Team _ Eason

[romoguy15]

Hey @v-easonf-msft  I have tried my best to upload a sample file with what I am trying to achieve with the calculation. I understand it can be quite compliacted to try and decipher what i'm trying to do. Basically my problem in my Percentage calculation is I don't know how to identify row by row the week number to divide it by in order to get a cumulative percentage total week by week

 https://1drv.ms/u/s!AqID1H0nHPOzhBPUVCHQy_avV8-r?e=htQs8T