Forum Discussion

J_Eastwood's avatar
J_Eastwood
Frequent Visitor
2 months ago
Solved

Matrix period showing incorrect total when using previous year measure

I have a dataset that shows items with a received date and I have set up a separate calendar table matching the date in the calendar table, to the received date in the dataset. The calendar also has a date hierarchy for fiscal year, fiscal quarter, and month name - where April to June is Q1, July to September is Q2 etc. to override PBI using calendar quarters.

 

The report is filtered on the page to show the last 16 months only, as we are only interested in the last 5 quarters and one of the key visualisations is a matrix showing the quarterly counts (using the fiscal year hierarchy), the count of items (a DISTINCTCOUNT measure of item ref to exclude duplicated IDs), the previous year count (which is a calculation of the count measure, where DATEADD is -1 year), and a percentage change measure showing the difference between count and prev. count.

 

This works fine across the small amount of Q4 24/25 that falls into the last 16 months and all of 25/26 and the quarter breakdowns for 26/27, however the total for 26/27 is completely different than the sum shown in Q1 and Q2 (shows up to 1st July, which is why there is one entry in Q2):

I presume this is due to being a current ongoing year and a confusion over the previous year count measure and the fiscal quarters, but it's odd the quarter works fine, but the Total for previous year doesn't (it is correct for 25-26).

 

Measures / calculated columns show below

 

Prev Year Count =
CALCULATE(
    [CSINs Count],
    DATEADD(
        'Calendar'[Dates],
        -1,
        YEAR
    )
)
 
CSINs Count = DISTINCTCOUNT(Table_owssvr[CSPRP Ref])

 

Fiscal Quarter =
 VAR fqNum = QUARTER(EDATE('Calendar'[Dates],-3))

 RETURN
    CONCATENATE("Q",CONVERT(fqNum,STRING))

 

 

Need Help
  • Rupa01's avatar
    Rupa01
    2 months ago

    Hi J_Eastwood,

    You can try the following measure instead of using [Prev Year Count] directly in the matrix –

    Prev Year Count Display = 
IF(
    ISINSCOPE('Calendar'[Fiscal Quarter]),
    [Prev Year Count],
    SUMX(
        SUMMARIZE(
            'Calendar',
            'Calendar'[Fiscal Year],
            'Calendar'[Fiscal Quarter]
        ),
        CALCULATE([Prev Year Count])
    )
)
    • At the Fiscal Quarter level, it returns the original [Prev Year Count] value.
    • At the Fiscal Year total level, Power BI normally recalculates the measure for the entire year, which can produce unexpected totals when using DISTINCTCOUNT.
    • This measure overrides that behavior by summing the quarter-level previous year values, making the Fiscal Year total align with the visible quarter rows.

    Use [Prev Year Count Display] in the matrix instead of [Prev Year Count] and check whether the Fiscal Year totals now match your expected results.

     

    💡 Helpful? Give a Kudos 👍 — keep the community growing
    ✅ Solved your issue? Mark as Solution ✔️ — help others find it faster

    Best regards,
    Rupasree Achari | BI & Fabric Analytics Engineer

8 Replies

  • Rupa01's avatar
    Rupa01
    Icon for Solution Sage rankSolution Sage
    2 months ago

    Hi J_Eastwood,

    You can try the following measure instead of using [Prev Year Count] directly in the matrix –

    Prev Year Count Display = 
IF(
    ISINSCOPE('Calendar'[Fiscal Quarter]),
    [Prev Year Count],
    SUMX(
        SUMMARIZE(
            'Calendar',
            'Calendar'[Fiscal Year],
            'Calendar'[Fiscal Quarter]
        ),
        CALCULATE([Prev Year Count])
    )
)
    • At the Fiscal Quarter level, it returns the original [Prev Year Count] value.
    • At the Fiscal Year total level, Power BI normally recalculates the measure for the entire year, which can produce unexpected totals when using DISTINCTCOUNT.
    • This measure overrides that behavior by summing the quarter-level previous year values, making the Fiscal Year total align with the visible quarter rows.

    Use [Prev Year Count Display] in the matrix instead of [Prev Year Count] and check whether the Fiscal Year totals now match your expected results.

     

    💡 Helpful? Give a Kudos 👍 — keep the community growing
    ✅ Solved your issue? Mark as Solution ✔️ — help others find it faster

    Best regards,
    Rupasree Achari | BI & Fabric Analytics Engineer
    • J_Eastwood's avatar
      J_Eastwood
      Frequent Visitor
      2 months ago

      thank you, I will look into this, but have used the accepted solution measure in the meantime

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    2 months ago

    Hi,

    Does this measure work?

    Prev Year Count = CALCULATE([CSINs Count],sameperiodlastyear('Calendar'[Dates]))
    • J_Eastwood's avatar
      J_Eastwood
      Frequent Visitor
      2 months ago

      thanks for the response, this gives the same quarter subtotal as my version. I have used the accepted solution instead

  • v-achippa's avatar
    v-achippa
    Icon for Community Support rankCommunity Support
    2 months ago

    Hi J_Eastwood,

     

    Thank you for reaching out to Microsoft Fabric Community.

     

    Thank you Rupa01, parry2k and Ashish_Mathur for the prompt response.

     

    As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by the user's for the issue worked? or let us know if you need any further assistance.

     

    Thanks and regards,

    Anjan Kumar Chippa