Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi everyone,
I'm a new user both to the forum and the world of power pivots/power bi so please bear with me!
I am rollng forward a file that I have inherited at work, and i need all of the previous years movements for the balance sheet to work, I've rolled forward everything else in the file and now it seems that my prior year only picks up last year movements, but there are 3 prior years that need to be included.
The formulas in place are these:
BS GBP YTD:=IF(ISERROR([BS YTD]/CALCULATE(min(FX_rates[value]),filter(CALENDAR,CALENDAR[DATE]),filter(FX_rates,FX_rates[Currency]="GBP"))),0,[BS YTD]/CALCULATE(min(FX_rates[value]),filter(CALENDAR,CALENDAR[DATE]),filter(FX_rates,FX_rates[Currency]="GBP")))+[PY BS 1617]
PY BS 1617:=if(ISFILTERED(CALENDAR[FISCALYEAR]),CALCULATE(TOTALYTD([EUR TOTAL],PREVIOUSYEAR(CALENDAR[DATE],"31-03"),all(CALENDAR[PERIOD]),"31-03"))+
CALCULATE(TOTALYTD(SUMX(OpeningBSPY,OpeningBSPY[Amount]),PREVIOUSYEAR(CALENDAR[DATE],"31-03"),all(CALENDAR[PERIOD]),"31-03")))
The PY BS 1617 formula is looking to a list of adjustments that need to be included as these aren't in sage over the years (OpeningBSPY)
All of the data is within the file for all years.. Can anyone help? I can assume i need to change something within the CALENDAR section...
If you need any more infor to help them pelae tell me , as I said.. totally new to this world!!
Thanks 🙂
For any time intelligence function, you could implement a custom DAX formula.
https://www.sqlbi.com/articles/time-intelligence-in-power-bi-desktop/
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
108 | |
105 | |
87 | |
74 | |
66 |
User | Count |
---|---|
124 | |
112 | |
96 | |
80 | |
72 |