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.
I have a table with following columns: fiscal year and the amount (See the table below).
TABLE1
Fiscal_Year | Amount |
2002 | $100 |
2002 | $200 |
2003 | $75 |
2003 | $95 |
2003 | $37 |
2004 | $30 |
2004 | $90 |
2005 | $45 |
2006 | $99 |
Also, I have a fiscal year table. This table has One to Many relationship with TABLE1.
FY_Table
Fiscal_Year |
2002 |
2003 |
2004 |
2005 |
2006 |
I want to calculate the previous year's amount.
Fiscal_Year | Amount | LY_Amount |
2002 | $300 | |
2003 | $207 | $300 |
2004 | $120 | $207 |
2005 | $45 | $120 |
2006 | $99 | $45 |
Following is my trial code which seems to give me all blank in the LY_Amount:
measure--> LY_Amount =
var LY_Fiscal_Year = SELECTEDVALUE(FY_Table[Fiscal_Year])-1
return (calculate(sum(TABLE1[Amount]), FILTER(TABLE1, TABLE1[Fiscal_Year] = LY_Fiscal_Year)))
Solved! Go to Solution.
Thanks for all your help. I used the following code.
var LY_Fiscal_Year = SELECTEDVALUE(TABLE1[Fiscal_Year])-1
return (calculate(sum(TABLE1[Amount]), all(TABLE1), TABLE1[Fiscal_Year]= LY_Fiscal_Year)))
@Anonymous , Try like
var LY_Fiscal_Year = SELECTEDVALUE(FY_Table[Fiscal_Year])-1
return (calculate(sum(TABLE1[Amount]), FILTER(all(FY_Table), FY_Table[Fiscal_Year]= LY_Fiscal_Year)))
Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA
Nope, Nothing. The result is coming all blank
Fiscal_Year | Amount | LY_Amount |
2002 | $300 | BLANK |
2003 | $207 | BLANK |
2004 | $120 | BLANK |
2005 | $45 | BLANK |
2006 | $99 | BLANK |
Thanks for all your help. I used the following code.
var LY_Fiscal_Year = SELECTEDVALUE(TABLE1[Fiscal_Year])-1
return (calculate(sum(TABLE1[Amount]), all(TABLE1), TABLE1[Fiscal_Year]= LY_Fiscal_Year)))
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 |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |