Forum Discussion

hawkeyes12's avatar
hawkeyes12
Frequent Visitor
4 years ago

QoQ Measure in Matrix Visual; pulling in previous values when I filter

Hello,

 

I am using the Matrix visual and one of the values is a measure thats calculating the QoQ Change. My QoQ Change Formula is: 

VAR __PREV_QUARTER =
    CALCULATE(
        SUM(Query1[Score]),
        DATEADD('Calendar'[Date], -1, QUARTER)
    )
RETURN
    SUM(Query1[Score]) - __PREV_QUARTER
 
 
In addition to the Matrix visual, I have a Quarter Slicer and a Rating Slicer on the page. The rating slicer has three values: High, Medium, and Low. However, when I filter on quarter slicer (for example to Q4) and the rating slicer lets say to "Medium", the Matrix updates to show data in Q4 with a "Medium" rating, but also pulls in rows for Q3 data that had a "Medium" Rating (but the Q4 Rating is not Medium).
 
Any ideas on what I'm doing wrong? If I remove the QoQ Measure from the Matrix, and filter on Q4 and "Medium" only the current quarter's rows appear. Therefore, I'm thinking I need to adjust my formula, but am not sure how...
Need Help

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    hawkeyes12 , This formula, only moving date backward. And will take Rating or any other dimension filter

    CALCULATE(
    SUM(Query1[Score]),
    DATEADD('Calendar'[Date], -1, QUARTER)
    )

     

    You need change it if you do not want rating filter

  • hawkeyes12's avatar
    hawkeyes12
    Frequent Visitor
    4 years ago

    Hi amitchandak  thank you for the quick response! Unfortunately, this is still not working for me. When I filter for Q4 and "Medium" rating, the Matrix still includes rows from Q3 that have a "Medium" rating, although the rating is showing as "Blank" but its still pulling those rows in...

  • v-xiaotang's avatar
    v-xiaotang
    Community Support
    4 years ago

    Hi hawkeyes12 

    Thanks for reaching out to us. But info is not enough to go on. 

    Usually information below is needed, could you provide it? Thanks.

     (1) a sample file, you can replace raw data with bogus data to protect your privacy.

         or provide some sample data that fully covers your issue/question.

    (2) give your expected result based on the sample you provide. If calculation formula is involved, please provide it.

    Thanks.

     

    Best Regards,

    Community Support Team _Tang

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