Forum Discussion

JoycevanWijk's avatar
JoycevanWijk
Frequent Visitor
2 years ago
Solved

P & L statement with calculation groups, maybe I have made it too difficult

I used a tutorial to create calculation groups for my P&L,    I have a table FinancialData and it has realisation and budget lines. The type is R for realisation and B for budget.    I want...
Need Help
  • danextian's avatar
    danextian
    2 years ago

    Hi JoycevanWijk 

     

    If you want to exclude just the first month from showing YTD, you can just write

    IF (
    [Current Month Number] => 1,
    CALCULATE (
        CALCULATE ( SELECTEDMEASURE (), DATESYTD ( Dates[Date] ) ),
        SAMEPERIODLASTYEAR ( Dates[Date] )
    )
)

     

    I think my 'problem' is, that I don't have an entire column with percentages, but one row. And the row is for the structure. What do you mean? Isn't your calculation item supposed to return %?

danextian's avatar
danextian
Icon for Super User rankSuper User
2 years ago

Hi JoycevanWijk ,

 

After some testing, I realized that custom format is applied to each item in a calculation group. If you have the latest version of Power BI, calcuation group is now native and the formatting for each item can be easily customized. Go to either relationship/diaghram or DAX query view to find Calculation Group. 

 

 

In order to hide YTD values, I used this formula below which checks for the max month number in the current filter context and compares it with the current row month number. It is to return the desired value only if the two measures match.

YTD PY = 
IF (
    [Current Month Number] = [Max Month Number],
    CALCULATE (
        CALCULATE ( SELECTEDMEASURE (), DATESYTD ( Dates[Date] ) ),
        SAMEPERIODLASTYEAR ( Dates[Date] )
    )
)

 


 Please see attached sample pbix for details. 

Note: You might not be able to open it properly if you're using an older version of Power BI Desktop.

JoycevanWijk's avatar
JoycevanWijk
Frequent Visitor
2 years ago

Thank you for looking into this. 

 

I checked the string, and it is what it should be. 

I think my 'problem' is, that I don't have an entire column with percentages, but one row. And the row is for the structure. 

 

I see what you are doing with YTD last year, but I have a financial report of the current year, with the financial data for every month, and I don't want to see a YTD in the first month, since that is the equal to the first month, just a minor issue. 🙂

 

 

 

 

  • danextian's avatar
    danextian
    Icon for Super User rankSuper User
    2 years ago

    Hi JoycevanWijk 

     

    If you want to exclude just the first month from showing YTD, you can just write

    IF (
    [Current Month Number] => 1,
    CALCULATE (
        CALCULATE ( SELECTEDMEASURE (), DATESYTD ( Dates[Date] ) ),
        SAMEPERIODLASTYEAR ( Dates[Date] )
    )
)

     

    I think my 'problem' is, that I don't have an entire column with percentages, but one row. And the row is for the structure. What do you mean? Isn't your calculation item supposed to return %?

    • JoycevanWijk's avatar
      JoycevanWijk
      Frequent Visitor
      2 years ago

      I will try to make a dummy file of this with fake data, because it is hard to explain. 

       

      Changing to => 1 results in only showing profit and margin every month and blanks on every other row item. 

       

      I have two calculation groups:

       

      FS Structure is on the row in the matrix and time intelligence is in the column of the matrix. 

       

       

      I want the percentage on row level bruto marge % in every column cell. 

       

      There is no % item in the time intelligence group.