Hi
In my power pivot model I am running financial actuals vs Budget vs Prior Year. The fiscal year runs from 1 April to 31 March.
I have created the calendar with the fiscal year updated, and budget pulls through correctly (so if I select March, it gives me the full budget from April to March). This is because in the YTD measure for budget I have the following formula:
TOTALYTD([Sum of Budget],'Calendar'[Date],"3/31")
For Prior Year I cant get the fiscal year to work. If I put in March, it only calculates for Jan - March and the formula it "accepts" is
=CALCULATE(TOTALYTD([Sum of Actual],SAMEPERIODLASTYEAR('Calendar'[Date])))
When I try add in the step of adding "3/31" to this SAMEPERIOD calculation I get this error: Too many arguments were passed to the SAMEPERIODLASTYEAR function. The maximum argument count for the function is 1.
Hope you can help
Thanks
Hi Declan,
Try:
=CALCULATE(TOTALYTD([Sum of Actual],'Calendar'[Date],"3/31"),SAMEPERIODLASTYEAR('Calendar'[Date]))
Mynda
Thanks so much Mynda, that solved it