cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
MarcUrdang Helper IV
Helper IV

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
1 ACCEPTED SOLUTION

Accepted Solutions
Super User I
Super User I

Re: creating a %

@MarcUrdang 

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




If this post has helped you, please give it a thumbs up!
Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

8 REPLIES 8
Super User I
Super User I

Re: creating a %

@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




If this post has helped you, please give it a thumbs up!
Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






MarcUrdang Helper IV
Helper IV

Re: creating a %

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

Super User I
Super User I

Re: creating a %

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




If this post has helped you, please give it a thumbs up!
Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






MarcUrdang Helper IV
Helper IV

Re: creating a %

hi

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

thanks

Marc

Super User I
Super User I

Re: creating a %

@MarcUrdang 

I sent you my email address via PM




If this post has helped you, please give it a thumbs up!
Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






MarcUrdang Helper IV
Helper IV

Re: creating a %

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

Super User I
Super User I

Re: creating a %

@MarcUrdang 

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

 

I hope that solves it!

 




If this post has helped you, please give it a thumbs up!
Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Super User I
Super User I

Re: creating a %

@MarcUrdang 

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




If this post has helped you, please give it a thumbs up!
Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors