Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
MarcUrdang
Post Patron
Post Patron

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

@MarcUrdang 

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





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
PaulDBrown
Community Champion
Community Champion

@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





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.






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

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





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.






hi

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

thanks

Marc

@MarcUrdang 

I sent you my email address via PM





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.






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

@MarcUrdang 

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





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 

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

 

I hope that solves it!

 





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.






Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.