Forum

Error in Power Pivo...
 
Notifications
Clear all

Error in Power Pivot trying to calculate Prior Year YTD with a Fiscal Year

3 Posts
2 Users
0 Reactions
127 Views
(@decco)
Posts: 16
Eminent Member
Topic starter
 

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 

 
Posted : 10/03/2021 4:48 am
(@mynda)
Posts: 4761
Member Admin
 

Hi Declan,

Try:

=CALCULATE(TOTALYTD([Sum of Actual],'Calendar'[Date],"3/31"),SAMEPERIODLASTYEAR('Calendar'[Date]))

Mynda

 
Posted : 10/03/2021 5:49 am
(@decco)
Posts: 16
Eminent Member
Topic starter
 

Thanks so much Mynda, that solved it

 
Posted : 10/03/2021 8:57 am
Share: