Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Filtering years within a measure

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!
Need Help
  • v-joesh-msft's avatar
    v-joesh-msft
    6 years ago

    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.

     

3 Replies

  • Nathaniel_C's avatar
    Nathaniel_C
    Community Champion
    6 years ago

     

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        v-joesh-msft
        Solution Sage
        6 years ago

        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.