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.
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!
Solved! Go to Solution.
Hi @Anonymous ,
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
Hi @Anonymous ,
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
@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!
Hi @Anonymous ,
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
Hi @Anonymous ,
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
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 |
---|---|
110 | |
98 | |
78 | |
64 | |
56 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |