Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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/
User | Count |
---|---|
101 | |
90 | |
78 | |
70 | |
69 |
User | Count |
---|---|
110 | |
96 | |
95 | |
74 | |
71 |