Hi im new to poweerbi and getting there slowly learing . I have spent all day pulling my hair out trying to work this out. Using loads of different ways to know affect.
I have large dataset, that has been pulled from Cisco. I also have amoungst other tables a table I created to hold KPI values. The table looks like this(below). Now the region field is linked to table which hold every call that comes via its region field. I have a spreate date table which works out a number of different date values based on start date of the call.
I need to create gauage chart against both KPI's for 60sec and 120 sec of anwser
I was hoping I could create a measure for 60 and one for 120. By pulling correct value from the table.
The page this is going to work on. Already filters the region from a slicer, so I assume this would reduce my table to 2 relevant rows? I was then hoping I could find the row that corropsonds to KPI. Then go along the columns to one which matches our fisical year. Current year is worked out from formular below
VAR _currentFiscalYear = YEAR(today())+ QUOTIENT(MONTH(today())+ (13-4),13)
(Code above works out our fisical year that starts in April)
Once relvant row has been matched then give me corosponding value.
| Region | KPI (seconds) | 2021 | 2022 | 2023 |
| region1 | 60 | 80 | 85 | 88 |
| region1 | 120 | 90 | 93 | 95 |
| region2 | 60 | 75 | 75 | 78 |
| region2 | 120 | 85 | 88 | 91 |
Any help would be much appricated on this
