creating a %

[MarcUrdang]

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

[PaulDBrown]

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

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]

@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.

[MarcUrdang]

hi

Unfortunately our firewalls prevent this .. I can mail it to you though?

thanks

Marc

[PaulDBrown]

@MarcUrdang 

I sent you my email address via PM

[MarcUrdang]

I sent through a mail about 30 mins ago .. thanks so much

[PaulDBrown · Accepted solution]

@MarcUrdang 

Sent you a new email with column calculations. Sorry about the confusion. 

[PaulDBrown]

@MarcUrdang 

Just sent you an email with a copy of your file.

I hope that solves it!