Forum Discussion

GuestUser's avatar
GuestUser
Icon for Helper V rankHelper V
6 years ago
Solved

Profit and Loss Reports

Hi All,   I need to create PnL statement . I have below tables   Table1 : Master table - Dimension   Rowid    Level 5           Level 6 1              Profit             Sales 2              ...
Need Help
  • mwegener's avatar
    mwegener
    6 years ago

    Hi GuestUser ,

     

    we call this a schema similar to Business Central.

     

    We use the following way.

    You may download my PBIX file from here.
    Hope this helps.

     

    The cool thing is that the values are calculated at runtime and so other filters, such as Calendar are taken into account.

     

    Regards,

    Marcus

    Dortmund - Germany
    If I answered your question, please mark my post as solution, this will also help others.
    Please give Kudos for support.

GuestUser's avatar
GuestUser
Icon for Helper V rankHelper V
6 years ago

Hi,

 

One more help pls

Along with COGS , if I need COGS% , then can u help me out where exactly I need to add the DAX formula

 

Formula : COGS% = (COGS/Total Sales)*100

 

Report layout is somewhat like below

Level 6                                  Current year      Prev Year

Sales                                        1000               1233
COGS                                      200                 1333
COGS %                                   20%
Profit Before Depreciation       1200                 1444
Depreciation                              300                         1333
Profit After Depreciation           900                 111
Income                                      400                      1000

mwegener's avatar
mwegener
Icon for Most Valuable Professional rankMost Valuable Professional
6 years ago

Hi GuestUser ,

 

it's not that easy, but it is possible.
You must add a row to the schema and an explicit calculation to the measure.

 

You may download my PBIX file from here.
Hope this helps.

 

Regards,

Marcus

Dortmund - Germany
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.

  • mwegener's avatar
    mwegener
    Icon for Most Valuable Professional rankMost Valuable Professional
    6 years ago

    Hi GuestUser 


    If I answered your question, please mark my post as solution, this will also help others.
    Please give Kudos for support.

    • GuestUser's avatar
      GuestUser
      Icon for Helper V rankHelper V
      6 years ago

      Hi mwegener ,

       

      I am trying to add previous year sales with below query

       

      CALCULATE(sum('Fact'[Amount]),SAMEPERIODLASTYEAR('DATE'[Calender Date]))
       
      But blank Value is being displayed. Any suggesions pls?
      • mwegener's avatar
        mwegener
        Icon for Most Valuable Professional rankMost Valuable Professional
        6 years ago

        Hi GuestUser,

         

        You may download my PBIX file from here.
        Hope this helps.

         

        Regards,

        Marcus

        Dortmund - Germany
        If I answered your question, please mark my post as solution, this will also help others.
        Please give Kudos for support.