pcowman1 Tracker

Re: Creating BOM Availability

pcowman1 — Fri, 07 Feb 2020 04:07:42 GMT

Thank you! It's late here and I've been working since early but it looks perfect! Many thanks. If I ever see you, I owe you a drink.

Re: Creating BOM Availability

pcowman1 — Fri, 07 Feb 2020 01:33:51 GMT

I'm going to add to this (and I really think this should be simple and don't know why I'm having a hard time):

if (sum(Inventory in Category) = min(sum(inventory in Category of all category sums))

Is that clearer? Am i making it worse?

Re: Creating BOM Availability

pcowman1 — Thu, 06 Feb 2020 19:15:34 GMT

Say I'm making these two parts - Item 1 has a bottle neck at Category 1 because there are only 2.

Item 2 has a bottle neck at Category 2 because there are only 20 and the rest of the categories are larger.

Common Item Number	Item_Category_Code	Part Number	Inventory
Item 1	Category 1	Part 1	2
Item 1	Category 2	Part 2	5
Item 1	Category 3	Part 3	15
Item 1	Category 3	Part 4	31
Item 1	Category 3	Part 5	59
Item 1	Category 5	Part 6	63
Item 1	Category 6	Part 7	168
Item 1	Category 4	Part 8	254
Item 1	Category 3	Part 9	398
Item 1	Category 3	Part 10	647
Item 1	Category 5	Part 11	725
Item 1	Category 3	Part 12	2000
Item 2	Category 1	Part 13	50
Item 2	Category 2	Part 14	20
Item 2	Category 4	Part 15	8
Item 2	Category 3	Part 16	15
Item 2	Category 4	Part 17	25
Item 2	Category 3	Part 18	31
Item 2	Category 5	Part 19	63
Item 2	Category 4	Part 20	127
Item 2	Category 6	Part 21	168
Item 2	Category 3	Part 22	398
Item 2	Category 3	Part 23	647
Item 2	Category 5	Part 24	725
Item 2	Category 3	Part 25	2000

So the end result would look like this:

Common Item Number	Item_Category_Code	Inventory
Item 1	Category 1	2
Item 2	Category 2	20

Re: Creating BOM Availability

pcowman1 — Thu, 06 Feb 2020 02:35:58 GMT

I'm seriously considering moving the whole thing to excel because I need to pivot a table and join it to another table. Might easier in excel where I can do vlookup on the columns but I'm really not that great with vlookup in excel either. I need to up my game in this area.

I fixed the first one by doing a second table that uses the common number and bom number and then does a calculated column filtering.

Re: Creating BOM Availability

pcowman1 — Wed, 05 Feb 2020 11:48:38 GMT

I'm trying for the second - I got No on both

Creating BOM Availability

pcowman1 — Wed, 05 Feb 2020 04:50:21 GMT

I'm trying to make a report for whether you can make a master part number or not, and then which sub-assemblies can be used. I'm connecting to a Business Central DB but that part doesn't really matter when you get to PBI.

Here's a sample of what the data looks like:

Part 1 can't be made because it has shortage in inventory

Item 2 can be made but has a bottle neck on Category 4.

The biggest part is - What is available to be made or not made.

Can this be done with a measure/filter? I'm not good with filters yet.

Re: Power Query Cross Join

pcowman1 — Thu, 09 Jan 2020 20:49:48 GMT

Just learned a fabulous new to me thing from this! LOVE IT!

Re: Using a Single Date as a Parameter for Aging AP/AR

pcowman1 — Mon, 23 Dec 2019 00:37:25 GMT

mwegenerI haven't had a chance to try yet. Maybe this week.

Re: Using a Single Date as a Parameter for Aging AP/AR

pcowman1 — Tue, 10 Dec 2019 20:36:01 GMT

I don't want to do a data refresh every time the date changes.

Using a Single Date as a Parameter for Aging AP/AR

pcowman1 — Tue, 10 Dec 2019 19:56:35 GMT

I'm trying to use a Parameter to change the date that I age AR and AP by. Usually I would just use Today() but instead I want to use the parameter AgeByDate. However, when I update the parameter in Desktop or in the service it doesn't change any of the numbers.

In M Query I created a column with the following:

#"Add Age" = Table.AddColumn(#"Filtered Rows", "Doc Age", each Duration.Days(@AgeByDate - [Document_Date]))

Re: Gateway to Excel - Error

pcowman1 — Fri, 22 Nov 2019 02:17:42 GMT

Wow, thank you. It's not super urgent yet, but if this doesn't work I will open a support ticket.

Gateway to Excel - Error

pcowman1 — Thu, 21 Nov 2019 17:26:46 GMT

We have tried several different user settings but keep getting the same issue. the path is correct.

Re: Refresh from Business Central very slow

pcowman1 — Mon, 11 Nov 2019 15:34:22 GMT

I mostly use pages. I've been trying to find training on extensions so that I can create my own queries but keep finding pieces and not a whole comprehensive guide.

It's possible that extensions are causing a problem for some of the pages load. I will look into it. Thanks.

It's weird that one customer it will be fine and another has issues on refresh.

Refresh from Business Central very slow

pcowman1 — Fri, 08 Nov 2019 04:10:54 GMT

While i'm not getting a fail, simple updates in Query are taking forever. Adding a column causes it to 'refresh' the table and that can take 5 minutes (or more). Has anyone else had this issue with BC? Is there a way to get it to stop refreshing the columns while working in Query?

Any tips or tricks would be greatly appreciated.

Re: Measure Y over Y some not working

pcowman1 — Thu, 24 Oct 2019 17:33:24 GMT

I'm using the standard Quick Measure:

Amount (LCY) YoY% =

IF(

ISFILTERED('Customer Summary'[Posting Date]),

ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),

VAR __PREV_YEAR =

CALCULATE(

SUM('Customer Summary'[Amount (LCY)]),

DATEADD('Customer Summary'[Posting Date].[Date], -1, YEAR)

)

RETURN

DIVIDE(SUM('Customer Summary'[Amount (LCY)]) - __PREV_YEAR, __PREV_YEAR)

)

Measure Y over Y some not working

pcowman1 — Thu, 24 Oct 2019 16:24:44 GMT

I'm having a problem with the Year Over Year Quick Measure where it won't show the difference where zeros are involved. Has anyone else had this issue?

Re: Gateway Issue - SQL and D365

pcowman1 — Thu, 22 Aug 2019 11:50:17 GMT

Thank you! Fixed now. Will be saving the page.

Gateway Issue - SQL and D365

pcowman1 — Wed, 21 Aug 2019 17:24:01 GMT

I've created a report which has connections to SQL on Prem and D365 (CRM) Odata.

When I published the report it is giving me an issue with the SQL Data Gateway. It wants the odata to go through it as well.

When I publish just Odata it correctly goes through the online source.

Has anyone else had this issue?

Re: pbiviz --create-cert Error unknown option

pcowman1 — Mon, 10 Jun 2019 10:53:55 GMT

thank you. That fixed it. i guess they haven't updated the instructions on the MS page to reflect the new code.

pbiviz --create-cert Error unknown option

pcowman1 — Fri, 07 Jun 2019 14:03:47 GMT

I'm getting an error when I use the command: pbiviz --create-cert

I installed the tools per: npm i -g powerbi-visuals-tools and it went fine.

Now I get this:

Has anyone else seen this?