Forum Discussion

Cridezi's avatar
Cridezi
Regular Visitor
1 year ago
Solved

Issues with Previous Years actuals

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's avatar
    Cridezi
    1 year ago

    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😀

     

     

8 Replies

  • FBergamaschi's avatar
    FBergamaschi
    Icon for Super User rankSuper User
    1 year ago

    Can you please share the pbix via some cloud service? 

     

    Thanks

    • FBergamaschi's avatar
      FBergamaschi
      Icon for Super User rankSuper User
      1 year ago

      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

  • Cridezi's avatar
    Cridezi
    Regular Visitor
    1 year ago

    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's avatar
      v-kpoloju-msft
      Icon for Community Support rankCommunity Support
      1 year ago

      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's avatar
        v-kpoloju-msft
        Icon for Community Support rankCommunity Support
        1 year ago

        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's avatar
    v-kpoloju-msft
    Icon for Community Support rankCommunity Support
    1 year ago

    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.