Filtering years within a measure

[Anonymous]

I am using a measure to calculate a value within a matrix, which is then presented for both 2018 and 2019. 

Extended Revenue divided by INVOICE_QTY =

DIVIDE(

    SUM('WEEKLY_INVOICE_REGISTER_DETAIL'[EXT Revenues]),

    SUM('WEEKLY_INVOICE_REGISTER_DETAIL'[INVOICE_QTY]))

I want another measure to find the difference between between the two values for 2018 and 2019. I have a date heirarchy for years set up as the columns in my table. I tried:

'2019-2018 Revenue Difference = CALCULATE(CALCULATE(

[Extended Revenue divided by INVOICE_QTY] , 'WEEKLY Tbl_MV_TM_INVOICE_REGISTER_DETAIL'[GL_YEAR]= "2019") - (CALCULATE(

[Extended Revenue divided by INVOICE_QTY] , 'WEEKLY Tbl_MV_TM_INVOICE_REGISTER_DETAIL'[GL_YEAR] = "2018")))

But its subtracting each yearly value from 0.

Thanks!

[Nathaniel_C]

Hi @Anonymous ,
If you break this into two measures, do they work? If you attach the following portion to a card, is it accurate?

CALCULATE(
[Extended Revenue divided by INVOICE_QTY] , 'WEEKLY Tbl_MV_TM_INVOICE_REGISTER_DETAIL'[GL_YEAR]= "2019")

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

[Anonymous]

Hi @Nathaniel_C ,

Thanks for the quick reply! This works great if I create a separate table and have both the 2018, 2019, and difference. When I put the difference measure into our main table it subtracts 0 from the 2019 value and the 2018 value from 0. I think this is because the matrix is using a date heirarchy, how would you access the year value within that into the individual revenue formulas?

[v-joesh-msft · Accepted solution]

Hi @Anonymous ,

Not quite sure what your data model looks like, maybe you can try the following measure:

'2019-2018 Revenue Difference =
IF (
    ISINSCOPE ( WEEKLY_INVOICE_REGISTER_DETAIL[Date].[Quarter] ),
    BLANK (),
    CALCULATE ( [Extended Revenue divided by INVOICE_QTY] )
        - CALCULATE (
            [Extended Revenue divided by INVOICE_QTY],
            FILTER (
                ALLSELECTED ( WEEKLY_INVOICE_REGISTER_DETAIL[Date].[Year] ),
                WEEKLY_INVOICE_REGISTER_DETAIL[Date].[Year]
                    < MAX ( WEEKLY_INVOICE_REGISTER_DETAIL[Date].[Year] )
            )
        )
)

Here is a demo, please try it:

https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EYIzmQIx0LJCrs-fuogh37gBSxi4dG1ekgTTw_ZWonO4Tw?e=PHyhcC 

If not your case, kindly share your sample pbix file if you don't have any Confidential Information.

Best Regards,

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