How to calculate year range and display data accordingly for selected year range

[ShrutiDande]

Hi Team,

below is the screenshot of my sample data and tried to add new colum for year range 

 I want to use the year range column  and month in the slicer .
I want to show the average of STM for the selected year range in matrix.(e.g. when i select the year range "22-23",

it should show me the average of STM for the selected month for both years in 2022 and 2023), but i am getting data in matrix only for 2023.

Not sure where i am doing wrong. 

Thanks!

[ShrutiDande]

@v-cgao-msft  Thanks a lot! it is  working!

[v-cgao-msft]

Hi @ShrutiDande ,

Please try create a unconnected table like this.

Table 2 = ADDCOLUMNS(VALUES('Date'[Year]),"StartYear",'Date'[Year]-1,"Year_Range",RIGHT([Year]-1,2)&"-"&RIGHT([Year],2))

and then create a measure for the matrix's value.

Measure = 
VAR _start_year = SELECTEDVALUE('Table 2'[StartYear])
VAR _end_year = SELECTEDVALUE('Table 2'[Year])
VAR _result = CALCULATE(AVERAGE('Table'[STM]),FILTER(ALLSELECTED('Date'[Year]),'Date'[Year]>=_start_year&&'Date'[Year]<=_end_year))
RETURN
_result

result.

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[ShrutiDande]

@v-cgao-msft  Thank you so much for response!
I think i misconyved my reqirement. 
When i click on 22-23 year range i want to see average STM for both years(22 and 23 ) in separate columns.
Not  a single column of data for year range.
Can you please help?
Thanks in advance!

[v-cgao-msft]

Hi @ShrutiDande ,

Please try.

Measure 2 = 
VAR _year = SELECTEDVALUE('Date'[Year])
VAR _year_selected = SELECTEDVALUE('Table 2'[Year])
VAR _filter = IF(_year<=_year_selected&&_year>=_year_selected-1,1)
VAR _result = IF(_filter,AVERAGE('Table'[STM])+0) --If you don't want use 0 to fill the blank, you can remove +0.
RETURN
_result

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[v-cgao-msft · Accepted solution]

Hi @ShrutiDande ,

Please try.

Measure 2 = 
VAR _year = SELECTEDVALUE('Date'[Year])
VAR _year_selected = SELECTEDVALUE('Table 2'[Year])
VAR _filter = IF(_year<=_year_selected&&_year>=_year_selected-1,1)
VAR _result = IF(_filter,AVERAGE('Table'[STM])+0) --If you don't want use 0 to fill the blank, you can remove +0.
RETURN
_result

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum