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 %?

JoycevanWijk's avatar
JoycevanWijk
Frequent Visitor
2 years ago

Thank you, 

 

the missing spaces was because of a text wrap in the row header. 🙂

for the percentage formatting. I am  using dummy measure because I am using calculation groups. So for one calculation item that is on the row, but showing data from a second calculation group in the columns I want to have a different formatting. Not sure if I'm explaning it correctly. 


This is the calculation item:

Hiding the YTD has the same problem. I don't want to apply it to a measure, but to a calculation group that is in the column of a matrix. 

 

 

 

  • 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 %?