Forum Discussion

Solfieri's avatar
Solfieri
Frequent Visitor
2 years ago
Solved

Value disapearing when using YTD measure on Matrix visualization

Problem Description: I am experiencing an issue with a Year-To-Date (YTD) measure in Power BI. When I insert this YTD measure into a matrix visualization, the Net Income value disappears. However, when I remove the YTD aspect and display the measure without YTD calculation, the Net Income value reappears, albeit incorrect.

 

Context:

  • I have created a YTD measure called "YTDNetIncome" using the DAX formula provided.
YTDNetIncome =
CALCULATE(
    TOTALYTD(
        CALCULATE([YTDOperatingIncome] + [YTDOtherIncome]),
        'Calendar'[Date]
    ),
    FILTER(
        ALL('Calendar'),
        'Calendar'[Year] = SELECTEDVALUE('Calendar'[Year]) &&
        'Calendar'[Month] <= SELECTEDVALUE('Calendar'[Month])
    )
)
 
  • The measure sums up the YTD Operating Income and YTD Other Income for each month.
YTDOperatingIncome = TOTALYTD(CALCULATE([YTDGrossProfit]-[YTDexpense]),'Calendar'[Date])
YTDOtherIncome = TOTALYTD(CALCULATE(sum(Transactions[netamt]),Transactions[longname]="Other Income"),'Calendar'[Date])
 
  • The formula is functioning correctly in other visualizations but not in a specific matrix visualization.

  

Issue Description:

  1. YTD Measure Disappears: When I add the "YTDNetIncome" measure to the matrix, the Net Income values disappear from the visualization.
  2. Incorrect Net Income: If I remove the YTD aspect and display the raw measure without YTD calculation, the Net Income values reappear, but they are incorrect.

I would greatly appreciate any insights into why the YTD measure is behaving unexpectedly in this specific matrix visualization. Additionally, suggestions for troubleshooting or refining the DAX formula would be incredibly helpful.

Thank you in advance for your assistance!


 

 
Need Help
  • MattAllington's avatar
    MattAllington
    2 years ago

    It's not clear if the measure in these 2 matrix is the same. I will assume so. The existence, or not, of a second measure in a matrix cannot affect the first measure, so that is not the cause. It seems the columns in rows in both matrixes are different. This is the likely cause. I suggest you create a new blank page, add 2 matrixes containing the measure in question, then build both matrixes. Watch carefully to see what works and what causes it not to work. Then investigate what stops it working. 

5 Replies

  • MattAllington's avatar
    MattAllington
    Icon for Community Champion rankCommunity Champion
    2 years ago

    It's not clear if the measure in these 2 matrix is the same. I will assume so. The existence, or not, of a second measure in a matrix cannot affect the first measure, so that is not the cause. It seems the columns in rows in both matrixes are different. This is the likely cause. I suggest you create a new blank page, add 2 matrixes containing the measure in question, then build both matrixes. Watch carefully to see what works and what causes it not to work. Then investigate what stops it working. 

    • Solfieri's avatar
      Solfieri
      Frequent Visitor
      2 years ago

      Thank you, the problem was a filter. As soon as I did your solution I found out the issue. 

       

       

    • Solfieri's avatar
      Solfieri
      Frequent Visitor
      2 years ago

      Hello, 
      They aren't the same measure, but both measures have a similar structure as I show bellow:
      1) The one that's working on income statement, and showing the right, value is built with this summary structure:

      All_SummaryItems =
      VAR SummaryItem = SELECTEDVALUE('Income Statement Names'[longname])
      RETURN
      SWITCH(TRUE(),
      SummaryItem = "Income", [Revenue],
      SummaryItem = "Margin Percent [%]", [Margin_Percent],
      SummaryItem = "Cost of Goods Sold",[CostofGoodsSold],
      SummaryItem = "Total Revenue", [Revenue],
      SummaryItem = "Gross Profit", [calc_GrossProfit],
      SummaryItem = "Expense", [Expense],
      SummaryItem = "Operating Income", [calc_OperatingIncome],
      SummaryItem = "Other Income", [OtherIncome],
      SummaryItem = "EBITDA", [i_EBITDA],
      SummaryItem = "Net Income", [YTDNetIncome])

      2) The one that doesn't accept the YTD measure use this struture:
      BS_SummaryItems =
      VAR SummaryItem = SELECTEDVALUE('Transactions_Groups'[longname])
      RETURN
      SWITCH(TRUE(),
      SummaryItem = "Bank", [Bank],
      SummaryItem = "Accounts Receivable", [Accounts Receivable],
      SummaryItem = "Other Current Asset", [Other Current Asset],
      SummaryItem = "Other Asset", [Other Asset],
      SummaryItem = "Fixed Asset", [Fixed Assets],
      SummaryItem = "Accounts Payable", [Accounts Payable],
      SummaryItem = "Credit Card", [Credit Card],
      SummaryItem = "Other Current Liability", [Other Current Liabilities],
      SummaryItem = "Long Term Liability", [Long Term Liabilities],
      SummaryItem = "Equity", [Equity],
      SummaryItem = "Net Income", [NetIncome]
      )

      3) But doing as you suggested I tried a new structure to see the results and I made a simple sum of the values in the Balance Sheet, also using a dimension table with no direct relationship with the values table, and as soon as I insert a YTD measure, the net income row disappear:
      TotalBalanceSheet = CALCULATE([Total Current Assets] + [Fixed Assets] + [Other Asset] + [Total Current Liabilities] + [Equity] + [YTDNetIncome] + [Long Term Liabilities])
      Thank you for your answer.
  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    2 years ago

    Hi,

    Does this revised simplified measure, reolve the mystery of the disappearance?

    YTDNetIncome = CALCULATE([YTDOperatingIncome] + [YTDOtherIncome],DATESYTD('Calendar'[Date])

     

    • Solfieri's avatar
      Solfieri
      Frequent Visitor
      2 years ago

      Hello,

      Unfortunately, the suggested solution did not resolve the issue. Upon inserting the YTD measures, the entire row disappears from the visualization.

       

       

      Thank you for your contribution.