Prior Year YTD Calculation using a template

In my model I have calculated 1 scenario Actual PY $ YTD ('000) that I cannot reason as to why it is not working in side my template.

My model is based very much on the video Sam did on Financial Reporting w/Power BI - : https://portal.enterprisedna.co/courses/enrolled/448571

I have also fundamentally shown the calculations correct based on this screen shot
The values I expect to see calculating are in the Financial Actuals PY YTD on page 2.

My calculation for Actual PY $ YTD ('000) I have used is noted below.
I have attached my pbix file
Any assistance would be appreciated.Example Financial Reporting2.pbix (318.0 KB)

Thanks Michael

Actual PY $ YTD ('000) = 
VAR CurrentItem = SELECTEDVALUE(ISTemplate[Items (Normalised)])

RETURN
SWITCH(TRUE(),
    CurrentItem = "Net Sales Third", [Net Sales Third Actual PY YTD],
    CurrentItem = "Net Sales", [Net Sales Actual PY YTD],
    CurrentItem = "Cost of Sales Total", [COGS Actual PY YTD],
    CurrentItem = "Gross Profit $", [Gross Profit Actual $ PY YTD],
    CurrentItem = "Gross Profit %", FORMAT( [Gross Profit Actual % PY YTD], "0.0%"),
    CurrentItem = "Personnel Expenses", [Personnel Expenses Actual PY YTD],
    CurrentItem = "EBITDA", [EBITDA Actual PY YTD],
    CurrentItem = "EBIT (Operating Profit Before Int & Taxes)", [EBIT Actual PY YTD],
    CurrentItem = "Profit Before Tax", [Profit Before Tax Actual PY YTD],
    CurrentItem = "Profit Before Tax %", Format ([Profit Before Tax % Actual PY YTD], "0.0%"),
        CALCULATE(CALCULATE([Financial Actuals PY], FILTER(FinancialTable,FinancialTable[Level 3 Group]= CurrentItem)), DATESYTD(Dates[Date])))

Hello @michael66,

Thank You for posting your query onto the Forum.

To achieve the expected results I’ve just tweaked the formula in your file. Below is the formula provided for the reference (Just refer last two lines of the formula) -

Actual PY $ YTD ('000) = 
VAR CurrentItem = SELECTEDVALUE(ISTemplate[Items (Normalised)])

RETURN
SWITCH(TRUE(),
    CurrentItem = "Net Sales Third", [Net Sales Third Actual PY YTD],
    CurrentItem = "Net Sales", [Net Sales Actual PY YTD],
    CurrentItem = "Cost of Sales Total", [COGS Actual PY YTD],
    CurrentItem = "Gross Profit $", [Gross Profit Actual $ PY YTD],
    CurrentItem = "Gross Profit %", FORMAT( [Gross Profit Actual % PY YTD], "0.0%"),
    CurrentItem = "Personnel Expenses", [Personnel Expenses Actual PY YTD],
    CurrentItem = "EBITDA", [EBITDA Actual PY YTD],
    CurrentItem = "EBIT (Operating Profit Before Int & Taxes)", [EBIT Actual PY YTD],
    CurrentItem = "Profit Before Tax", [Profit Before Tax Actual PY YTD],
    CurrentItem = "Profit Before Tax %", Format ([Profit Before Tax % Actual PY YTD], "0.0%"),
        TOTALYTD( [Financial Actuals PY] , Dates[Date] , 
            FinancialTable[Level 3 Group] = CurrentItem ) ) 

I’m also attaching the working of the PBIX file for the reference. Also just few days back I was referring to the article related to the “DATESYTD V/s TOTALYTD”. I’m also providing below the link of that article for the reference.

Hoping you find this useful and meets your requirements that you were looking for. :slightly_smiling_face:

Thanks and Warm Regards,
Harsh

Financial Reporting - Harsh.pbix (318.3 KB)

https://www.pbiusergroup.com/communities/community-home/digestviewer/viewthread?MessageKey=efac2079-32f3-4a42-92c9-12d83fe3d1cd&CommunityKey=b35c8468-2fd8-4e1a-8429-322c39fe7110&tab=digestviewer#:~:text=RE%3A%20TOTALYTD%20Vs%20DATESYTD&text=Hi%20Richie%2C-,DATESYSTD%20will%20return%20a%20column%20with%20the%20dates%20up%20to,the%20year%20up%20to%20now.

2 Likes

Hello @michael66,

Also I was trying to identify where the problem actually was. So the “FILTER” function actually created the problem here. Once I removed that function and used the filter as an argument under the CALCULATE the results were absolutely identical as you were expecting. Below is the revised formula (refer second last line) provided for the reference alongwith the screenshot of both the results -

Actual PY $ YTD ('000) - 2 = 
VAR CurrentItem = SELECTEDVALUE(ISTemplate[Items (Normalised)])

RETURN
SWITCH(TRUE(),
    CurrentItem = "Net Sales Third", [Net Sales Third Actual PY YTD],
    CurrentItem = "Net Sales", [Net Sales Actual PY YTD],
    CurrentItem = "Cost of Sales Total", [COGS Actual PY YTD],
    CurrentItem = "Gross Profit $", [Gross Profit Actual $ PY YTD],
    CurrentItem = "Gross Profit %", FORMAT( [Gross Profit Actual % PY YTD], "0.0%"),
    CurrentItem = "Personnel Expenses", [Personnel Expenses Actual PY YTD],
    CurrentItem = "EBITDA", [EBITDA Actual PY YTD],
    CurrentItem = "EBIT (Operating Profit Before Int & Taxes)", [EBIT Actual PY YTD],
    CurrentItem = "Profit Before Tax", [Profit Before Tax Actual PY YTD],
    CurrentItem = "Profit Before Tax %", Format ([Profit Before Tax % Actual PY YTD], "0.0%"),
        CALCULATE( [Financial Actuals PY] , 
            FinancialTable[Level 3 Group] = CurrentItem ,
            DATESYTD( Dates[Date] ) ) )

So now you can use either of the formulas in your PBIX file which I’ve provided in this post or in earlier post. And both are providing the same results.

Hoping you find this useful. :slightly_smiling_face:

Thanks and Warm Regards,
Harsh

Hi Harsh
Thanks for a great and detailed response.
Love your answer, so thorough and complete.
Regards
Michael

1 Like