Dynamic P&L Report from NAV Account Schedule

[Lucian]

Hello,

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

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:

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

[Lucian]

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:

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:

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

[ImkeF · Accepted solution]

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.

[Lucian]

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