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.
Gurus,
Have a requirement. Here's the scenario. Need to comput the PCL column using the formula below: (base table: Brrw):-
-------------------------------------------------------------------------------------
Report Date (Slicer) (This is a dropdown, chosen by the user)
dd-mon-yy <--- Field : Brrw[ReportDate]
Industry LnAmt CrdAmt PCL
AGRICULTURE 10000 20000 ?
livestock 5000 10000 ? <-- Sub Industry
Fruits & Veg 5000 10000 ?
OIL & GAS 20000 50000 ?
Exploration
Pipelines
-----------------------------------------------------------
1) Compute SUM(LnAmt), SUM(CrdAmt) where Brrw[ReportDate] > Report Date(Slicer) - 12 Group by Industry
2) PCL = SUM(LnAmt) / AVG(SUM(CrdAmt)) Group by Industry
Need to have the PCL values for Industry and Sub Industry (they are both hierarchies). The condition in Step 1, am unable to get my head around(Slicer value, can it be assigned to a variable?). Any idea welcome.
Thanks in advance
Solved! Go to Solution.
@Anonymous , You can have formula like
measure =
var _max =maxx(allselected(Date), Date[Date])
var _min = date(year(_max), month(_max)-12,day(_max))
return
calculate([measure], filter(Table, Table[Join Date]>=_min))
@Anonymous , You can have formula like
measure =
var _max =maxx(allselected(Date), Date[Date])
var _min = date(year(_max), month(_max)-12,day(_max))
return
calculate([measure], filter(Table, Table[Join Date]>=_min))
@amitchandak Excellent!! Thank you for your time. Works perfectly. (Powerbi community is just amazing)
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 |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |