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.
Start Date | End Date | Name | Balance | Start year | End year |
1/1/2014 | 12/31/2014 | XX | 100 | 2014 | 2014 |
1/1/2015 | 12/31/2015 | XX | 200 | 2015 | 2015 |
1/1/2016 | 12/31/2016 | XX | 300 | 2016 | 2016 |
8/1/2015 | 7/31/2016 | YY | 400 | 2015 | 2016 |
8/1/2016 | 7/31/2017 | YY | 500 | 2016 | 2017 |
8/1/2017 | 7/31/2018 | YY | 600 | 2017 | 2018 |
I am trying to calculate my balance exposure as of a random date between the start and end date. Let me know if you help me in this
1. I need to create a date dimension so thatIcan pick any date and say how much is the balance of that day
if I pick 9/1/2016 my exposure is - 300+500
2. I also a need a financial year exposure.Filter all rows with exposure in the year 2016 ( slicer by any year).In this case, the rows will be 3,4,5 as the start and end year can be in 2016 ...
3. Using the slicer( date dimension), I want to select all rows where the eg Like start date > date dimension -30 days or end date < date dimension +30 days
Any thoughts .Thanks for your help
Hi @summerpolice,
Create below measure. Dim is a calendar table which used as date dimension for users to pick up any date.
Sum balance = CALCULATE ( SUM ( 'time period'[ Balance] ), FILTER ( 'time period', AND ( 'time period'[Start Date] <= MAXX ( ALLSELECTED ( dim[Date] ), 'dim'[Date] ), 'time period'[ End Date] >= MAXX ( ALLSELECTED ( dim[Date] ), 'dim'[Date] ) ) ) )
Thanks,
Yuliana Gu
Thanks it helps but i am still running into few issues.
If i pick a range of date in "Dim" , how do i identify all the rows in "timeperiod" which fall in the range and get a sum of balance.
The exposure could be as of a day or for a range of days
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 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |