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.