cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ruliana
Helper I
Helper I

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!
1 ACCEPTED SOLUTION

Hi @ruliana ,

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-fuo... 

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.

 

View solution in original post

3 REPLIES 3
Nathaniel_C
Super User
Super User

 

Hi @ruliana ,
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

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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?

 

 

Hi @ruliana ,

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-fuo... 

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.

 

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!