cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Lucian Advocate II
Advocate II

Dynamic P&L Report from NAV Account Schedule

Hello,

I need to create an P&L Report defined into Navision's Account Schedule that look like this:

 

Image1-1.png

 

I have searched a lot of resources, and one is close enough to what I need, and I would like to thank @ImkeF for this great article: Easy Profit and Loss and other (account) scheme reports in Power BI and Power Pivot using DAX - the picture above is taken from the mentioned article in the https://www.thebiccountant.com/.

The problem is that I have the Account Schedule in this form:

AccountSchedule.png

With another article from the same author: Create list of accounts from NAV account schedules or COA totaling syntax I have managed to breake the Totaling column for Posting Accounts but I'm stuck (blinded) on how could I properly transform/manage the Formula totaling_type lines.

Is it possible in this scenario to get the report?

 

EDIT: A more concrete question would be: How can I "split" the formula rows (ex. 10..30) based on Row_No column into corresponding Totaling values from that row (ex. 10 => 11100..11299) so then could "split" it again into the corresponding accounts using formula from the "Create list of accounts..." article.

 

Kind Regards,

Lucian

1 ACCEPTED SOLUTION

Accepted Solutions
Super User III
Super User III

Re: Dynamic P&L Report from NAV Account Schedule

Hi @Lucian  

you can use my function to recursively solve this from here: https://www.thebiccountant.com/2017/02/14/dynamically-flatten-parent-child-hierarchies-in-dax-and-po... 

So first create a list of all items from the current row with my NAV-function that you've already found, expand and then use the above function to determine the levels / retrieve all parents.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

3 REPLIES 3
Lucian Advocate II
Advocate II

Re: Dynamic P&L Report from NAV Account Schedule

Hello again,

 

Meanwhile I think I get "closer", but I still need help. It seems that the main problem is to transform the Accout Schedules table.

So I try to start from a simpler model:

SimplifiedSched1.png

So the problem is how could I replace formula lines with the Totaling values from the corresponding Row_No .

For example 10..12 should be replaced  with 11100..11110|11300..11310|11500..11510

Basically something like this:

SimplifiedSched2-transformed.png

Splitting the Totaling and the "first level" formulas using the NAV.AccountListFromTotaling.pq from the article in the previous message I could solve it using a "self join" (maybe not the best option), but still remains one problem: The formula on the last line (please disregard the description from all of them).

In this case how could I convert

95|165 => 10..12|100..102 => 11100..11110|11300..11310|11500..11510|21200..21210|23200..23210|25000..25010

And this formula is "just" 2 levels but what if will be n levels?

 

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Invoked Custom Function" = Table.AddColumn(Source, "NAVSplit", each NAVSplit([Totaling], null, null)),
    #"Expanded NAVSplit" = Table.ExpandListColumn(#"Invoked Custom Function", "NAVSplit"),
    #"Added Conditional Column" = Table.AddColumn(#"Expanded NAVSplit", "LooukupFor", each if [Totaling_Type] = "Formula" then [NAVSplit] else ""),
    #"Merged Queries" = Table.NestedJoin(#"Added Conditional Column", {"LooukupFor"}, #"Added Conditional Column", {"Row_No"}, "Added Conditional Column", JoinKind.LeftOuter),
    #"Expanded Added Conditional Column" = Table.ExpandTableColumn(#"Merged Queries", "Added Conditional Column", {"NAVSplit"}, {"Added Conditional Column.NAVSplit"})
in
    #"Expanded Added Conditional Column"

 

 Any ideeas?

Kind Regards,

Lucian

Super User III
Super User III

Re: Dynamic P&L Report from NAV Account Schedule

Hi @Lucian  

you can use my function to recursively solve this from here: https://www.thebiccountant.com/2017/02/14/dynamically-flatten-parent-child-hierarchies-in-dax-and-po... 

So first create a list of all items from the current row with my NAV-function that you've already found, expand and then use the above function to determine the levels / retrieve all parents.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

Lucian Advocate II
Advocate II

Re: Dynamic P&L Report from NAV Account Schedule

Hello @ImkeF ,

 

I felt like I was missing something... it was just "another" good article from your site. 😉

Thank you very much for your help.

 

Kind Regards,

Lucian

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ Integration Forum Launch

‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Solution Authors
Top Kudoed Authors