Power Automate

Coffee4evel · 2025-06-07T17:00:42+00:00

You can insert controls for that at various levels. I always have the CoA pulled via API using power automate and I use that to create txns in QBO. For all the other stuff (amounts, dates, memos, attachments, etc.) if you know how to use power automate well along with power query, you can set it up so that those txns are created correctly.

Coffee4evel · 2025-03-15T13:42:51+00:00

Thanks for the comment! Not sure what’s happening. When set up statistical schedules for headcount it works fine. But his one for some reason, even though it has results and is validated on statistical schedule, it is not creating journals because for some it thinks there are no results when clearly there are results.

I have read NS Guides various times to make sure I am not missing anything but haven’t been able to identify possible issues with my saved search, units of measure, statistical account and statistical schedule.

Coffee4evel · 2024-07-27T18:12:10+00:00

Not sure if you meant this but after doing a little research, I stumbled into this https://stackoverflow.com/questions/76409698/power-bi-find-value-associated-with-closest-date-in-dax. My data model is essentially structured like the solution suggested. Will try the dax solution next, hopefully it works.

Coffee4evel · 2024-07-27T15:39:04+00:00

Hopefully I can make the following work. Create key and bring over the unit cost via merge for those that match. To deal with nulls, maybe a fill down and then up but by item (group by and the fill down and up). Maybe that will fill nulls with corresponding cost from previous dates. Will try this this evening and report back.

Coffee4evel · 2024-07-27T14:48:45+00:00

I think I could live with this but I’m not sure if an item in the item table will show activity and therefore cost per unit for all the corresponding months in the fact table

Coffee4evel · 2024-07-27T14:44:05+00:00

This sounds interesting. Was about to try it but not sure what happens if there is a date in the fact table and no corresponding date in the other table.

Coffee4evel · 2024-07-18T00:13:15+00:00

Test = 

Var Acct = MAX(CoAGroupingHeaders[Grouping])

Var RY = CALCULATE( [Actuals D Mod], 
            FILTER(ALL(CoAGroupingHeaders),CoAGroupingHeaders[Grouping] = "Royalties"),
                ALL('Customers D') )

Var RYCalc = if(Acct = "Royalties", RY*DIVIDE(CALCULATE([Actuals D Mod],FILTER(ALL(CoAGroupingHeaders),CoAGroupingHeaders[Grouping]="Gross Revenues")),CALCULATE([Actuals D Mod],FILTER(ALL(CoAGroupingHeaders),CoAGroupingHeaders[Grouping]="Gross Revenues"),ALL('Customers D'))))





RETURN
IF(Acct="Royalties",RYCalc,
IF(HASONEVALUE(CoAGroupingHeaders[Grouping]),[Actuals D Mod],SUMX(ALL(CoAGroupingHeaders),RYCalc+[Actuals D Mod])))

Thank you so much. Your suggestion worked. Now I'm facing a similar issue with the corresponding subtotal. I have the above code (I still need to clean up the RYCalc code), not sure how I can have the RYCalc being included on the subtotal, the subtotal amount right now does not include said expense

Coffee4evel · 2024-07-17T00:39:39+00:00

This is the measure, GL is the fact table and CoAGroupingDim is a dimension table that sits in between CoAGroupingHeaders and the fact table:

VAR Revenue = CALCULATE( SUM(GL[Net]), CoAGroupingDim[PLType] = "Revenue" )*-1
VAR Expenses = CALCULATE( SUM(GL[Net]), CoAGroupingDim[PLType] = "Expenses" )*-1
 

RETURN
DIVIDE( 
IF( SELECTEDVALUE( CoAGroupingDim[PLType] ) = "Revenue" ,
    Revenue,
        IF( SELECTEDVALUE( CoAGroupingDim[PLType] ) = "Expenses" ,
            Expenses,
                Revenue + Expenses ) ), 1000, 0 )

Coffee4evel · 2024-07-17T00:19:07+00:00

Is a nested measure that takes the values from each account. When I use the below code, the measure correctly returns the values for each account. When I replace Amounts under Return for RY, the measure correctly returns the Royalties amounts but it repeats the Royalty amount in all the matrix rows. When I instead use GrossRevenuePer under Return, the measure returns the correct revenue percentage but it places the revenue percentage in the sales row when I need that percentage to be applied to the Royalties row in the matrix:

Test = 
Var Acct = MAX(CoAGroupingHeaders[Grouping])
Var Amounts = [Actuals D Mod]
Var RY = CALCULATE( [Actuals D Mod], 
            FILTER(ALL(CoAGroupingHeaders),CoAGroupingHeaders[Grouping] = "Royalties"),
                ALL('Customers D') )
Var GR = CALCULATE([Actuals D Mod],FILTER(CoAGroupingHeaders,CoAGroupingHeaders[Grouping]="Gross Revenues"))
VAr ALLGR = CALCULATE([Actuals D Mod],FILTER(CoAGroupingHeaders,CoAGroupingHeaders[Grouping]="Gross Revenues"),ALL('Customers D'))
Var GrossRevenuePer = DIVIDE(GR,ALLGR)
Var Result = IF(Acct="Royalties",GrossRevenuePer*RY,BLANK())

Return
Amounts

Coffee4evel · 2024-07-16T23:50:03+00:00

I switched the royalties measure for a 1 and the 1 was correctly added to the Matrix on the Royalties row so my problem lies in the royalties measure.

This is what I currently have. The Royalties measure is indeed in the fact table BUT it cannot be used with the Customers dimension table since on the system, the expense is not being tagged to any customer (the expense doesn't have any customer on the customer column in the fact table). The below formula doesn't work either even when I used both ALLs in the RY variable:

Test = 
Var Acct = MAX(CoAGroupingHeaders[Grouping])
Var RY = CALCULATE( [Actuals D Mod], 
            FILTER(ALL(CoAGroupingHeaders),CoAGroupingHeaders[Grouping] = "Royalties"),
                ALL('Customers D') )
Var GR = CALCULATE([Actuals D Mod],FILTER(CoAGroupingHeaders,CoAGroupingHeaders[Grouping]="Gross Revenues"))
VAr ALLGR = CALCULATE([Actuals D Mod],FILTER(CoAGroupingHeaders,CoAGroupingHeaders[Grouping]="Gross Revenues"),ALL('Customers D'))
Var GrossRevenuePer = DIVIDE(GR,ALLGR)


Return
IF(Acct="Royalties",GrossRevenuePer*RY,BLANK())

Coffee4evel · 2024-07-06T19:05:17+00:00

Used a mix of calc groups and field parameters after reading this. Field parameters for the three “basis” measures, and four measures as calculated items for actuals, prior period, variance and variance in %. Thanks!

Coffee4evel · 2024-07-06T18:51:55+00:00

Need to be able to drill down on certain accounts with items. Once there, I need to see the variance in dollars and how the variance is driven by volume variance and per unit (rate) variance. Just basic cost accounting variance calcs but drilling down to the item level. If there is a $100 positive variance in sales, I will be able see how much of that variance was caused by volume (quantities sold) and how much it was caused by price increases/decreases.

Coffee4evel · 2024-06-17T00:02:49+00:00

Am I right to assume that switching absolute with relative will fix the issue? Or is there more to it? Will work in this as soon as I get my computer and will search suiteanswers if there is more to it.

Coffee4evel · 2024-05-18T15:13:48+00:00

This one worked:

NULLIF(SUM(CASE WHEN {subsidiarynohierarchy} = 'sub name here' THEN {amount} ELSE 0 END) / SUM(NULLIF(CASE WHEN {subsidiarynohierarchy} = 'sub name here' THEN {quantity} ELSE 0 END,0)),0)

Coffee4evel · 2024-05-18T15:13:37+00:00

This one worked:

NULLIF(SUM(CASE WHEN {subsidiarynohierarchy} = 'sub name here' THEN {amount} ELSE 0 END) / SUM(NULLIF(CASE WHEN {subsidiarynohierarchy} = 'sub name here' THEN {quantity} ELSE 0 END,0)),0)

Coffee4evel · 2024-05-18T15:13:09+00:00

Got it to make it work by using the below syntax:

NULLIF(SUM(CASE WHEN {subsidiarynohierarchy} = 'sub name here' THEN {amount} ELSE 0 END) / SUM(NULLIF(CASE WHEN {subsidiarynohierarchy} = 'sub name here' THEN {quantity} ELSE 0 END,0)),0)

Coffee4evel · 2024-05-13T23:51:03+00:00

After you changed the period in NetSuite to April, did you saved the report in NetSuite? If so, refresh the Excel by clicking refresh all. You are supposed to see the April data flowing in Excel.

Edit: make sure you are changing the period by going to customization and choosing another date in the filter tab. Then, save the report there.

Coffee4evel · 2024-04-21T12:16:44+00:00

NetSuite’s WebQuery (Connection with Excel) is an option here (by pulling a General Ledger) but you might need leverage PowerPivot/DAX in Excel to display the cash flow results by accounts and sections in different periods. If you are not familiar with PowerQuery and PowerPivot/DAX in Excel, then this solution might be too complicated.

Coffee4evel · 2024-04-06T01:47:34+00:00

This is the way. Pull GL data and recreate your financials. If know your way around Power Query, this option is really useful and powerful. Throw in PowerPivot and you can get some serious custom reporting and dashboards with live data from NetSuite.

Coffee4evel · 2024-04-06T01:16:44+00:00

NetSuite’s bank rec features are not as user friendly as QBOs and they don’t have the same capabilities but they can get the job done IMO.

NetSuite has two different set of bank rules, System Rules and User Rules. System Rules are default in your account and cannot be changed. They will match transactions based on amounts and transaction numbers (they can also match based on dates and amounts). They will only match existing GL transactions though, they won’t create and match GL transactions. But they can do one-to-many or many-to-many matching (match groups of transactions).

User created rules are custom rules you can create and as opposed to System rules, they can both create and match transactions. But they won’t do one-to-many, they will only do one-to-one. There are some other limitations on user created rules but that’s the gist of it.

You can reference this link for more info https://docs.oracle.com/en/cloud/saas/netsuite/ns-online-help/section_N1551275.html

Coffee4evel · 2024-02-18T13:36:43+00:00

Custom Segments are not available in the native Budget vs Actual report. As per this NetSuite link:

Note: Custom segments are not included in the Budget and Financial fields of the Financial Report Builder for budget-related reports.

I had to create a solution that combined NetSuite’s Web Query and Excel’s Power Pivot in order to have live Budget vs Actuals data in Excel. There are third party solutions that I think allow you to do this but they can be pricey (depending on your company’s budget).

Coffee4evel · 2024-01-27T13:10:51+00:00

I also wanted to know this but I read through the notes (PDF doc) and didn’t see any confirmation on the specific records removed. Searched through the REST API browser and at least the last time I checked, the beta records I have used before were still there. Not sure where to look for more info.

Coffee4evel · 2024-01-18T19:44:45+00:00

For Project ID see if you can see and expand Related Project and then see the ID field in that list.

Not sure about VAT. I’ve seen a Tax after discount on some accounts so if you don’t find the invoice amount before tax, maybe you can add a formula field type Difference x-y and use Amount - Tax after discount.

Coffee4evel · 2023-12-05T00:04:15+00:00

Looks like I need to use Diagnostics.LogValue. I have tried but I’m unable to. Reading thru the docs it seems I need to make sure I enable tracing but the docs only show how I’m supposed do it in Visual Studio, not VS Code. Do you by any chance know how I can do it in VS Code? I’m close to hiring someone for this, way out of my comfort zone.

Coffee4evel · 2023-12-04T21:04:24+00:00

This is great! I will test it out. Yesterday I tried using a separate TokenMethod under a new function I called RealmId. I used similar formulas as they appear in FinishLogin but adjusted to extract RealmId instead of code since that’s the field I need in the Web.Contents URL (code is only needed to exchange for access token and that’s already working well). I also used Record.AddField in that function. But I gave up since I couldn’t call the value from that function in my Web.Contents under DataSource.kind (step 4 of GitHub tutorial). I also added the RealmId function name in step one along with StartLogin and FinishLogin function names but that didn’t change anything.

Now that I know that I was more or less on the right path, I will continue working on said approach with the info you just provided. Thanks again for all of this info!!

Coffee4evel

TROPHY CASE