topic Re: Cumulative Percentage Total By Fiscal Week in DAX Commands and Tips

Cumulative Percentage Total By Fiscal Week

romoguy15 — Fri, 09 Sep 2022 16:23:23 GMT

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

Re: Cumulative Percentage Total By Fiscal Week

v-easonf-msft — Fri, 09 Sep 2022 10:12:08 GMT

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

Re: Cumulative Percentage Total By Fiscal Week

romoguy15 — Fri, 09 Sep 2022 16:51:55 GMT

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

Re: Cumulative Percentage Total By Fiscal Week

romoguy15 — Wed, 14 Sep 2022 16:46:43 GMT

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

Re: Cumulative Percentage Total By Fiscal Week

romoguy15 — Fri, 16 Sep 2022 15:28:29 GMT

Hi v-easonf-msft , any thoughts?

Re: Cumulative Percentage Total By Fiscal Week

romoguy15 — Wed, 21 Sep 2022 14:31:52 GMT

Bump anyone?

Re: Cumulative Percentage Total By Fiscal Week

romoguy15 — Wed, 28 Sep 2022 17:00:16 GMT

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.

Re: Cumulative Percentage Total By Fiscal Week

romoguy15 — Wed, 28 Sep 2022 17:18:13 GMT

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.

Re: Cumulative Percentage Total By Fiscal Week

romoguy15 — Thu, 29 Sep 2022 20:48:30 GMT

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.