Forum Discussion

MarcUrdang's avatar
MarcUrdang
Icon for Post Patron rankPost Patron
6 years ago
Solved

creating a %

Good day .. please can you help me with creating a % .. I will explain my current measures and show you my calcs thus far. Hopefully you can guide me on the next steps? Measure used is SHIPMENTS (for reference)

 

1. I have 2 dates in my data which I subtract one from the other:  

CUI: = DATEDIFF(Input[Dest ETA],Input[Event (DDI / CUI%)],DAY)

 

2. The metric states that if the difference of #1 is less than a particular number then its deemed 'IN' else "OUT"

CUI: in out SEA = IF(Input[CUI:] <= -10,"IN","OUT")
 
3. I then need to see how many were "IN" for the previous month and I use the calc below to see how many shipments were 'IN". TM stands for 'this month'. So TM-1 = last month, TM-2 = 2 months back
TM -1 =
CALCULATE (
COUNT ( Input[Shipment ID]),
FILTER (
Input,
Input[Dest ETA]
>= DATE ( YEAR ( today() ), MONTH ( TODAY() ) - 1, 1 )
&& Input[Dest ETA] < DATE ( YEAR ( TODAY() ), MONTH ( TODAY() ), 1 )
)
)
 
Now I can see how many shipments were IN for last month .. and for 2 months back
 
4.  So now I need to calculate the % In vs Out for TM-1 vs TM-2 as a %
 
So if TM-1 = 10 in vs 15 out (therefore 10/25 = 40%
vs
TM-2 = 14 in vs 8 out (therefore 14/22 = 63,64%
 
I need to then subtract the one from the other to show month on month variance which would then show -23.64%
 
Please can you advise on point 4? Sorry for so much explanation but I want to make sure I help you understand what I need.
 
thanks so much 🙂
Marc
Need Help

8 Replies

  • PaulDBrown's avatar
    PaulDBrown
    Icon for Community Champion rankCommunity Champion
    6 years ago

    MarcUrdang 

     

    Can you share some sample data (fake if need be)? You can either post it (in table form if possible) or in a file on a cloud service - OneDrive, Google Drive, Dropbox ...)

    Thanks

    • MarcUrdang's avatar
      MarcUrdang
      Icon for Post Patron rankPost Patron
      6 years ago

      Hi there

       

      I have actually fixed this myself .. however now I am getting a circular reference error for which I need help. I have two lots measures. One is CUI and the other is CLR ...  with about 7 calcs running one off the other .. however on the last calc of CLR I am getting a circ ref error while on hte CUI I am not .. I have looked through each and every applicable calc and just cannot find the issue. If I mail you the .pbix can you see if you can find it?

       

      Its very puzzling as it all looks perfect

      thanks

      Marc

      • PaulDBrown's avatar
        PaulDBrown
        Icon for Community Champion rankCommunity Champion
        6 years ago

        MarcUrdang 

        Happy to give it a try... If you can upload it to a cloud service (OneDrive, Dropbox...) you can either post the link here or PM me it.