Prior Year YTD Calculation using a template

DAX DAX Calculations
1

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

image
image1167×771 158 KB

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.

image
image946×443 104 KB

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])))
2

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 ) )

Resultant Output
Resultant Output1432×802 60 KB

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.

DATESYTD V/s TOTALYTD
DATESYTD V/s TOTALYTD730×291 24.6 KB

3 Likes
3

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] ) ) )

Resultant Output
Resultant Output1427×799 61.1 KB

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

4

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

1 Like