Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Volume Calculation

Hi PBI Fox,   I'm struggling to find a solution on how to solve my problem. Can someone help me how to fix it?   I want to get the diff. between each month by category and by meter name. Below is...
Need Help
Tutorial Requests
  • v-xicai's avatar
    v-xicai
    5 years ago

    Hi Anonymous ,

     

    You may create a measure like DAX below to get diff value per month. While it won't achieve your requirement for the layout of Matrix visual completely, you may try to put [Equipment]and  [MeterName] into Rows box of Matrix, put [Category] into Columns box, put [Month] and [Diff Value per month] into Values box.

     

    Diff Value per month =
VAR _PreMonth =
    CALCULATE (
        SUM ( Table1[MeterValue] ),
        FILTER (
            ALLEXCEPT ( Table1, [Category], [Equipment], [MeterName] ),
            YEAR ( Table1[Date] ) = YEAR ( MAX ( Table1[Date] ) )
                && MONTH ( Table1[Date] )
                    = MONTH ( MAX ( Table1[Date] ) ) - 1
        )
    )
VAR _CurMonth =
    CALCULATE (
        SUM ( Table1[MeterValue] ),
        FILTER (
            ALLEXCEPT ( Table1, [Category], [Equipment], [MeterName] ),
            YEAR ( Table1[Date] ) = YEAR ( MAX ( Table1[Date] ) )
                && MONTH ( Table1[Date] ) = MONTH ( MAX ( Table1[Date] ) )
        )
    )
RETURN
    _CurMonth - _PreMonth

     

    Best Regards,

    Amy 

     

    Community Support Team _ Amy

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.