Financial Statements Template changing the order of Actuals, VAR VsLastYear to (Act - Act LY) error


New to the Forum, Good Day all!.

I was attempting to rework the Scenario Method Workshops Financial Reporting Template sept 2018 Dashboard (which I have to say is a totally cool piece of kit!) everything works and makes sense except when in the Actual Dax formula I try to change the order of the Variable VsLastYear such that the Claculate([Actual]… reads actual - Actual LY (instead of Act LY - Act) as per the model. The DAX formula seems to have no errors however when I use that Dax in the visual I get the error unmentioned. Hoep someone can help and see what I am missing, let me know if you need further clarification. Let me know if you need the PBIX file or a link to the the version

Thanks in advance


You know you can add images and formula directly into the posts. That is preferred if possible thanks.

With those formula images I’m unable to actually see the formula.

The likely issue is that one of the calculations being completed in the revised formula is evaluating to TEXT instead of a number.

This to me is all you would need to change.

Have a look through the formula and see if you can find where this is happening. You can always wrap the formula in VALUE( ). That’s a quick way to make it numeric.

Still having a slight challenge with this one, perhaps a fresh pair of eyes on this will quickly locate the Gremlin. Have attached a 2 minute video to make it I hope clearer.

Thanks for your time on this guys


FRT.pbix (824.6 KB)

Thanks for your patience on this one.

Just had a good look at it and this is the issue here.

The additional CALCULATE needed to be used with the DATEADD function.

That nailed it. Outstanding.

Sam it sucks not to have your Skill Set!

Doesn’t take to long. Just keep working on a variety of your own examples and it will quickly all make much more sense.

Leverage off the available showcases as well. They are very detailed and show a variety of techniques that can be copied.