cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper V
Helper V

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
Highlighted
Super User IV
Super User IV

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
Highlighted
Helper V
Helper V

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

Highlighted
Super User IV
Super User IV

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

Highlighted
Helper V
Helper V

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

August Community Highlights

Check out a full recap of the month!

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors