Issues with Previous Years actuals

[Cridezi]

Hi everyone,

I'm currently building a financial statement in Power BI using a matrix visual, and I'm running into an issue with my "Previous Year Actuals" measure. Everything works perfectly for current year actuals, but when I bring in the "Previous Year Actuals" measure, certain rows — specifically, the calculated subtotal lines like Gross Margin, EBITDA, etc. — are not displayed in the matrix. See snip from powerbi in the end of this messsage. Thanks for all the help!

ActualsPL and Previous Year Actuals measures are below. 

"ActualsPL =

VAR _SelectedHeader = SELECTEDVALUE (tbl_ChartofAccounts[Level 0])

VAR _Actuals = [ValuePL]

VAR _Revenue = CALCULATE(

    [ValuePL],

    REMOVEFILTERS(tbl_ChartofAccounts),

    tbl_ChartofAccounts[Level 0] = "Revenue"

)

VAR _GrossMargin = CALCULATE([ValuePL],

                REMOVEFILTERS(tbl_ChartofAccounts),

                tbl_ChartofAccounts[Level 0] = "Revenue"

                || tbl_ChartofAccounts[Level 0] = "Other operating income"

                || tbl_ChartofAccounts[Level 0] = "COGS"

)

VAR _EBITDA = CALCULATE([ValuePL],

                REMOVEFILTERS(tbl_ChartofAccounts),

                tbl_ChartofAccounts[Level 0] = "Revenue"

                || tbl_ChartofAccounts[Level 0] = "Other operating income"

                || tbl_ChartofAccounts[Level 0] = "COGS"

                || tbl_ChartofAccounts[Level 0] = "Operating expenses"

)

VAR _EBIT = CALCULATE([ValuePL],

                REMOVEFILTERS(tbl_ChartofAccounts),

                tbl_ChartofAccounts[Level 0] = "Revenue"

                || tbl_ChartofAccounts[Level 0] = "Other operating income"

                || tbl_ChartofAccounts[Level 0] = "COGS"

                || tbl_ChartofAccounts[Level 0] = "Operating expenses"

                || tbl_ChartofAccounts[Level 0] = "D&A"

)

VAR _NetIncome = CALCULATE([ValuePL],

                REMOVEFILTERS(tbl_ChartofAccounts),

                tbl_ChartofAccounts[Level 0] = "Revenue"

                || tbl_ChartofAccounts[Level 0] = "Other operating income"

                || tbl_ChartofAccounts[Level 0] = "COGS"

                || tbl_ChartofAccounts[Level 0] = "Operating expenses"

                || tbl_ChartofAccounts[Level 0] = "D&A"  

                || tbl_ChartofAccounts[Level 0] = "Financial income and expenses"

                || tbl_ChartofAccounts[Level 0] = "Appropriations"

                || tbl_ChartofAccounts[Level 0] = "Taxes"

)

VAR _GrossMarginPct = DIVIDE(_GrossMargin, _Revenue)

VAR _EBITDAPct = DIVIDE(_EBITDA, _Revenue)

VAR _EBITPct = DIVIDE(_EBIT, _Revenue)

VAR _Result =

    SWITCH(TRUE(),

        _SelectedHeader = "Gross Margin", _GrossMargin,

        _SelectedHeader = "Gross Margin, %", _GrossMarginPct,

        _SelectedHeader = "EBITDA", _EBITDA,

        _SelectedHeader = "EBITDA, %", _EBITDAPct,

        _SelectedHeader = "EBIT", _EBIT,

        _SelectedHeader = "EBIT, %", _EBIT,

        _SelectedHeader = "Net Income", _NetIncome,

        _Actuals

    )

RETURN _Result"

"Previous Year Actuals =

VAR SelectedDates = VALUES('Calendar'[Date])

VAR ShiftedDates =

    ADDCOLUMNS(SelectedDates, "PYDate", DATEADD('Calendar'[Date], -1, YEAR))

VAR PYFilter =

    TREATAS(SELECTCOLUMNS(ShiftedDates, "Date", [PYDate]), 'Calendar'[Date])

VAR Level0 = SELECTEDVALUE(tbl_ChartofAccounts[Level 0])

RETURN

CALCULATE(

    [ActualsPL],

    PYFilter,

    KEEPFILTERS(tbl_ChartofAccounts[Level 0] = Level0)

)"

I’ve also tried using SAMEPERIODLASTYEAR measure for the , but that causes an error in the matrix and nothing is shown. 
"Previous Year Actuals =
CALCULATE(
[ActualsPL],
SAMEPERIODLASTYEAR('Calendar'[Date])
)"

[Cridezi · Accepted solution]

Thanks all for the help - Now it works. I don't really know what I did, but at least I made the Calendar query again and that seemed to fix everything😀

[v-kpoloju-msft]

Hi @Cridezi,

Glad to hear that it is working now. Sometimes just rebuilding a query helps clear out hidden issues like step mismatches or type conflicts. Recreating the Calendar query likely refreshed the schema and resolved any underlying inconsistencies.

let me know what you find after giving these steps a try happy to help you investigate this further.
Thank you for using the Microsoft Community Forum.

[v-kpoloju-msft]

Hi @Cridezi,

Just checking in to see if the issue has been resolved on your end. If the earlier suggestions helped, that is great to hear! And if you’re still facing challenges, feel free to share more details happy to assist further.

Thank you.

[v-kpoloju-msft]

Hi @Cridezi,

Hope you had a chance to try out the solution shared earlier. Let us know if anything needs further clarification or if there's an update from your side always here to help.

Thank you.

[v-kpoloju-msft]

Hi @Cridezi,

Just wanted to follow up one last time. If the shared guidance worked for you, that’s wonderful hopefully it also helps others looking for similar answers. If there’s anything else you'd like to explore or clarify, don’t hesitate to reach out.

Thank you.

[v-kpoloju-msft]

Hi @Cridezi,

Thank you for reaching out to the Microsoft fabric community forum. I reproduced the scenario, and it worked on my end. I used my sample data and successfully implemented it.

You might also find this community thread helpful it discusses a similar issue: Actuals vs YTD Actuals vs Previous Year YTD Actual Matrix

I am also including .pbix file for your better understanding, please have a look into it:

Hope this helps clarify things and let me know what you find after giving these steps a try happy to help you investigate this further.

Thank you for using the Microsoft Community Forum.

[FBergamaschi]

Can you please share the pbix via some cloud service? 

Thanks

[FBergamaschi]

A simple approach like this

CALCULATE(

        [ActualsPL],

        SAMEPERIODLASTYEAR( 'Calendar'[Date] )

    )

should work

If it does not, as the post author says, I think we need to see the model

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI