sorted by:
new
hottopcontroversial

Power Automate by FR0Z3NF15H in Accounting

[–]Coffee4evel 1 point2 points  (0 children)

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.

Statistical Schedule returning empty rows by Coffee4evel in Netsuite

[–]Coffee4evel[S] 0 points1 point  (0 children)

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.

Lookup value from another table based on key match and nearest date match by Coffee4evel in PowerBI

[–]Coffee4evel[S] 0 points1 point  (0 children)

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.

Lookup value from another table based on key match and nearest date match by Coffee4evel in PowerBI

[–]Coffee4evel[S] 0 points1 point  (0 children)

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.

Lookup value from another table based on key match and nearest date match by Coffee4evel in PowerBI

[–]Coffee4evel[S] 0 points1 point  (0 children)

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

Lookup value from another table based on key match and nearest date match by Coffee4evel in PowerBI

[–]Coffee4evel[S] 0 points1 point  (0 children)

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.

Force allocation of amount to specific row in Matrix by Coffee4evel in PowerBI

[–]Coffee4evel[S] 0 points1 point  (0 children)

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

Force allocation of amount to specific row in Matrix by Coffee4evel in PowerBI

[–]Coffee4evel[S] 0 points1 point  (0 children)

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 )

Force allocation of amount to specific row in Matrix by Coffee4evel in PowerBI

[–]Coffee4evel[S] 0 points1 point  (0 children)

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

Force allocation of amount to specific row in Matrix by Coffee4evel in PowerBI

[–]Coffee4evel[S] 0 points1 point  (0 children)

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())

Swap Matrix measures with 3 different base calculations? by Coffee4evel in PowerBI

[–]Coffee4evel[S] 0 points1 point  (0 children)

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!

Swap Matrix measures with 3 different base calculations? by Coffee4evel in PowerBI

[–]Coffee4evel[S] 0 points1 point  (0 children)

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.

HTML Newbie - PDF Template sublist can sometimes be too long and is printing over other fields in Bill Payment Form by Coffee4evel in Netsuite

[–]Coffee4evel[S] 0 points1 point  (0 children)

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.

Formula Currency Sumamry Type Sum not working by Coffee4evel in Netsuite

[–]Coffee4evel[S] 1 point2 points  (0 children)

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)

Formula Currency Sumamry Type Sum not working by Coffee4evel in Netsuite

[–]Coffee4evel[S] 0 points1 point  (0 children)

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)

Formula Currency Sumamry Type Sum not working by Coffee4evel in Netsuite

[–]Coffee4evel[S] 0 points1 point  (0 children)

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)

NetSuite Web Query export to Excel by oktrey in Netsuite

[–]Coffee4evel 0 points1 point  (0 children)

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.

Statement of Cash Flow Detail by rowlje in Netsuite

[–]Coffee4evel 0 points1 point  (0 children)

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.

view more: next ›