Matrix - show data from most recent month in Yearly/Quarterly drill down

[mrothschild]

I'm using the following Measure to pull in balance sheet values by "Item" or account description.  It's working as intended when Matrix columns are drilled down to month, but when drilled up to Quarter or Year, it doesn't show any data for Q3 or 2019.  

I'm guessing this is an easy fix but I can't seem to figure it out.

Thanks!

Excel data file

Measure = 
TOTALMTD(
    SUMX(
        GROUPBY('_Monthly Balance Sheet','_Monthly Balance Sheet'[Balance Sheet Item],'_Monthly Balance Sheet'[Balance])
        ,'_Monthly Balance Sheet'[Balance]
    ),
    '_Monthly Balance Sheet'[Month]
)

[v-lili6-msft]

HI @mrothschild 

First, we couldn't download your sample pbix file, please re-upload again.

Then for formula, it is a simple TOTALMTD measure, it corresponds to the following code:

FILTER (
        ALL ( 'Date'[Date] ),
        AND (
            'Date'[Date] <= MAX ( 'Date'[Date] ),
            AND (
                YEAR ( 'Date'[Date] ) = YEAR ( MAX ( 'Date'[Date] ) ),
                MONTH ( 'Date'[Date] ) = MONTH ( MAX ( 'Date'[Date] ) )
            )
        )
    )

It will show the max month value for current row context.

When drilled up to Quarter or Year, it will shows the max month value in Q3 or 2019, and it wokrs well on my side,

Why it doesn't show any data for Q3 or 2019 in your report?

If you still have problem, Please share your sample pbix file and your expected output, there should be something in other.

Regards,

Lin

[mrothschild]

Thanks for your response, @v-lili6-msft .  I tried adding the filter you suggested, but still not getting the desired result.  

PBIX file is now in Dropbox, so shouldn't have any issues, nor require login to access

Below is a screenshot of the Matrix visual with columns drilled to Yearly, Quarterly, and Monthly.  The desired output is that the data in July when drilled down monthly appears in the column for 2019 in the Yearly drill down and in the Q3 - 2019 column when drilled down quarterly.

Thanks

[v-lili6-msft · Accepted solution]

hi @mrothschild 

For your case, just adjust the report as below:

Step1:

Add a Calendar YearMonthNumber column in "_Monthly Balance Sheet" table

Calendar YearMonthNumber = YEAR('_Monthly Balance Sheet'[Month])*12-1+MONTH('_Monthly Balance Sheet'[Month])

Step2:

Adjust the [Balance - Month-end - Sub-total] formula as below:

Balance - Month-end - Sub-total =

    CALCULATE(

            SUMX(

            GROUPBY('_Monthly Balance Sheet','_Monthly Balance Sheet'[Balance Sheet Item],'_Monthly Balance Sheet'[Balance])

            ,'_Monthly Balance Sheet'[Balance]

            ),

        FILTER('_Formatting Table','_Formatting Table'[Group Type]=9),

        FILTER (

            ALL ( 'Date' ),

            AND (

                'Date'[Date] <= MAX ( 'Date'[Date] ),

                AND (

                    YEAR ( 'Date'[Date] ) = YEAR ( MAX ( 'Date'[Date] ) ),

                    'Date'[Calendar YearMonthNumber] = MAX('_Monthly Balance Sheet'[Calendar YearMonthNumber])

                )

            )

        )

    )

Result:

Regards,

Lin

[mrothschild]

Thanks!  Worked perfectly