Home Forums Power Pivot 20 day order rate

This topic contains 1 reply, has 1 voice, and was last updated by  jdc0105 3 months ago.

Viewing 2 posts - 1 through 2 (of 2 total)
• Author
Posts
• #15502

jdc0105
Participant
• Started: 1
• Replies: 1
• Total: 2

Hi all,

I hope you can help me!

I am trying to compare the 20 Day Order Rate current fiscal versus last fiscal year. We work with an American Fiscal Year system (1 Fisc Year is 13 Periods).

My X-axis includes the actual data of fiscal year 2018. The Last Year 20 day order rate is thus full year; my current year 20 day order rate should only show my first 4 closed periods. In this graph I want to show 2018 and 2019 (parallel periods)

The problem I have, is with the current 20 day order rate – so until period 4 it is ok, but then my amount goes down due to the fact that the total amount of orders is decreasing and is still being divided by 20 – so after another 20 days my calculation finally stops.

To resolve this I calculated another one (version 2) which is giving me the correct numbers until period 4 closing, but then afterwards continues with a fixed number.

Formulas:

<span style=”font-size: medium;”>Last Year:=(</span><span style=”color: #008000; font-size: medium;”>calculate</span><span style=”font-size: medium;”>(</span><span style=”color: #008000; font-size: medium;”>sum</span><span style=”font-size: medium;”>([Order History Value]);DatesTable[IsWeekday]=</span><span style=”color: #008000; font-size: medium;”>true</span><span style=”font-size: medium;”>;</span><span style=”color: #008000; font-size: medium;”>datesbetween</span><span style=”font-size: medium;”>(DatesTable[ActualDate];</span><span style=”color: #008000; font-size: medium;”>FIRSTDATE</span><span style=”font-size: medium;”>(</span><span style=”color: #008000; font-size: medium;”>DATEADD</span><span style=”font-size: medium;”>(DatesTable[ActualDate];(-27);</span><span style=”color: #008000; font-size: medium;”>day</span><span style=”font-size: medium;”>));</span><span style=”color: #008000; font-size: medium;”>lastdate</span><span style=”font-size: medium;”>(DatesTable[ActualDate])))+</span><span style=”color: #008000; font-size: medium;”>calculate</span><span style=”font-size: medium;”>(</span><span style=”color: #008000; font-size: medium;”>sum</span><span style=”font-size: medium;”>([Sched Agree Order Value]);DatesTable[IsWeekday]=</span><span style=”color: #008000; font-size: medium;”>true</span><span style=”font-size: medium;”>;</span><span style=”color: #008000; font-size: medium;”>datesbetween</span><span style=”font-size: medium;”>(DatesTable[ActualDate];</span><span style=”color: #008000; font-size: medium;”>FIRSTDATE</span><span style=”font-size: medium;”>(</span><span style=”color: #008000; font-size: medium;”>DATEADD</span><span style=”font-size: medium;”>(DatesTable[ActualDate];(-27);</span><span style=”color: #008000; font-size: medium;”>day</span><span style=”font-size: medium;”>));</span><span style=”color: #008000; font-size: medium;”>lastdate</span><span style=”font-size: medium;”>(DatesTable[ActualDate]))))/20</span>

<span style=”font-size: medium;”>Current Year V1:=(</span><span style=”color: #008000; font-size: medium;”>calculate</span><span style=”font-size: medium;”>(</span><span style=”color: #008000; font-size: medium;”>sum</span><span style=”font-size: medium;”>([Order History Value]);DatesTable[IsWeekday]=</span><span style=”color: #008000; font-size: medium;”>true</span><span style=”font-size: medium;”>;</span><span style=”color: #008000; font-size: medium;”>datesbetween</span><span style=”font-size: medium;”>(DatesTable[ActualDate];</span><span style=”color: #008000; font-size: medium;”>FIRSTDATE</span><span style=”font-size: medium;”>(</span><span style=”color: #008000; font-size: medium;”>DATEADD</span><span style=”font-size: medium;”>(DatesTable[ActualDateFiscNY];(-27);</span><span style=”color: #008000; font-size: medium;”>day</span><span style=”font-size: medium;”>));</span><span style=”color: #008000; font-size: medium;”>lastdate</span><span style=”font-size: medium;”>(DatesTable[ActualDateFiscNY])))+</span><span style=”color: #008000; font-size: medium;”>calculate</span><span style=”font-size: medium;”>(</span><span style=”color: #008000; font-size: medium;”>sum</span><span style=”font-size: medium;”>([Sched Agree Order Value]);DatesTable[IsWeekday]=</span><span style=”color: #008000; font-size: medium;”>true</span><span style=”font-size: medium;”>;</span><span style=”color: #008000; font-size: medium;”>datesbetween</span><span style=”font-size: medium;”>(DatesTable[ActualDate];</span><span style=”color: #008000; font-size: medium;”>FIRSTDATE</span><span style=”font-size: medium;”>(</span><span style=”color: #008000; font-size: medium;”>DATEADD</span><span style=”font-size: medium;”>(DatesTable[ActualDateFiscNY];(-27);</span><span style=”color: #008000; font-size: medium;”>day</span><span style=”font-size: medium;”>));</span><span style=”color: #008000; font-size: medium;”>lastdate</span><span style=”font-size: medium;”>(DatesTable[ActualDateFiscNY]))))/20</span>

<span style=”font-size: medium;”>Current Year V5:=</span><span style=”color: #008000; font-size: medium;”>calculate</span><span style=”font-size: medium;”>((</span><span style=”color: #008000; font-size: medium;”>calculate</span><span style=”font-size: medium;”>(</span><span style=”color: #008000; font-size: medium;”>sum</span><span style=”font-size: medium;”>([Order History Value]);DatesTable[IsWeekday]=</span><span style=”color: #008000; font-size: medium;”>true</span><span style=”font-size: medium;”>;</span><span style=”color: #008000; font-size: medium;”>datesbetween</span><span style=”font-size: medium;”>(DatesTable[ActualDate];</span><span style=”color: #008000; font-size: medium;”>FIRSTDATE</span><span style=”font-size: medium;”>(</span><span style=”color: #008000; font-size: medium;”>DATEADD</span><span style=”font-size: medium;”>(DatesTable[ActualDateFiscNY];(-27);</span><span style=”color: #008000; font-size: medium;”>day</span><span style=”font-size: medium;”>));</span><span style=”color: #008000; font-size: medium;”>lastdate</span><span style=”font-size: medium;”>(DatesTable[ActualDateFiscNY])))+</span><span style=”color: #008000; font-size: medium;”>calculate</span><span style=”font-size: medium;”>(</span><span style=”color: #008000; font-size: medium;”>sum</span><span style=”font-size: medium;”>([Sched Agree Order Value]);DatesTable[IsWeekday]=</span><span style=”color: #008000; font-size: medium;”>true</span><span style=”font-size: medium;”>;</span><span style=”color: #008000; font-size: medium;”>datesbetween</span><span style=”font-size: medium;”>(DatesTable[ActualDate];</span><span style=”color: #008000; font-size: medium;”>FIRSTDATE</span><span style=”font-size: medium;”>(</span><span style=”color: #008000; font-size: medium;”>DATEADD</span><span style=”font-size: medium;”>(DatesTable[ActualDateFiscNY];(-27);</span><span style=”color: #008000; font-size: medium;”>day</span><span style=”font-size: medium;”>));</span><span style=”color: #008000; font-size: medium;”>lastdate</span><span style=”font-size: medium;”>(DatesTable[ActualDateFiscNY]))))/20;DatesTable[IsClosedPeriod])</span>

###### Attachments:
You must be logged in to view attached files.
#15504

jdc0105
Participant
• Started: 1
• Replies: 1
• Total: 2

graph

###### Attachments:
You must be logged in to view attached files.
Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic.