I calculated % of Revenue in the excel sheet I import, and used the following formulae to pull through current month data:
CY MTD % TORE =
VAR CURRENTITEM = SELECTEDVALUE( ‘Summary P&L template’[Income Statement Items] )
RETURN
SWITCH( TRUE() ,
CURRENTITEM = “Total Revenues”, FORMAT(CALCULATE( [Income statement values % TORE], ‘Company A data % TORE’[Items (Normalized)] = “Total Revenues”) , “0.0%”) ,
CURRENTITEM = “Rooms Revenues”, FORMAT( CALCULATE( [Income statement values % TORE], ‘Company A data % TORE’[Items (Normalized)] = “Rooms Revenues”) , “0.0%”) , etc.
And the below formula to pull through last year same month data:
LY MTD % TORE =
CALCULATE( [CY MTD % TORE], SAMEPERIODLASTYEAR(‘DATE’[Month & Year]))
The above populate my table perfectly, however, when I run the variance column:
MTD vs. LY % TORE = [CY MTD % TORE] - [LY MTD % TORE] , it gives me the following error message:
The exact same process worked when comparing values.
Since you’re calculating only the % figures, you can try by removing the “FORMAT()” function from your measure and then you can simply apply the % formatting by selecting it under the “Measure Tools” option. In that way, you can still have the answer in terms of %.
After this, you can simply calculate the measure for the Variance Analysis by subtracting this year totals with the last years.
Because in this case, by applying the “FORMAT()” function you’re converting the numbers into the text format since it’s mentioned under the quotes “0.0%”.
And also I see that your last year total is also a text since it’s derived from CY MTD % TORE measure and you are trying to subtract this year’s total (which is text in this case) with the text. And therefore it’s showing an error. Also by wrapping a “VALUE()” Function around the “FORMAT()” will absolutely not work in this case.
It’s important to note that @sam.mckay has also mentioned in one of his video in Financial Reporting that it will show an error if we simply try to calculate the Variance in this manner.
I’m also attaching the screenshot of my working where I was able to calculate CY vs PY Actuals %.
And also attaching the post which I had on “VALUE()” Function.
Hoping you find this helpful and can help you to achieve your desired analysis.
Please feel free to write back in case I’ve missed out on anything.
