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,
I am trying to use Date Slicer in the Measure but unable to do it successfully. I have explained my problem below. I appreciate if someone can look into this.
Table Columns: Date (Last date of the month), Portfolio Id, Manager, % Return
Slicer: I have Date Slicer in the report. I take Min Date and Max Date from that Date Slicer.
Measure Average Return: This measure calculates % Average Return for the date selected.
Chart: Clustered Column Chart to show Measure AverageReturn by Manager.
Goal: I only want to include those portfolios for which data is available for all the months selected in the Date Slicer. For example if date slicer has selected entire 2017 & 2018. Then I want to include only those portfolios where we have data for all 24 months.
Issue: The chart includes all the Portfolios even if data is not available for all the months.
Measures I have created to achieve this:
nMonths = CountRows('Data') This correctly calculates number months for which data is available
FilteredNMonths =
This correctly calculates number of months Date Filter has selected.
Return = PRODUCT(Data[% Return])
AverageReturn =
Solved! Go to Solution.
Have a month year column in your table too. Take a count distinct of that. Count distinct of that should be equal to date diff on min and max dates . Even Month-year of date table should work
AverageReturn =
var _maxdate = calculate(MAX(Data[Date]),ALLSELECTED())
var _mindate = calculate(min(Data[Date]),ALLSELECTED())
var _diff = datediff(mindate,maxdate,month)
return
averagex(filter(SUMMARIZE(Data, Data[PorfolioId], "toAverage", [Return],"_dist" ,distinctcount(data[Month-year])),[_dist]=_diff),[Return])
Have a month year column in your table too. Take a count distinct of that. Count distinct of that should be equal to date diff on min and max dates . Even Month-year of date table should work
AverageReturn =
var _maxdate = calculate(MAX(Data[Date]),ALLSELECTED())
var _mindate = calculate(min(Data[Date]),ALLSELECTED())
var _diff = datediff(mindate,maxdate,month)
return
averagex(filter(SUMMARIZE(Data, Data[PorfolioId], "toAverage", [Return],"_dist" ,distinctcount(data[Month-year])),[_dist]=_diff),[Return])
@amitchandak wrote:Have a month year column in your table too. Take a count distinct of that. Count distinct of that should be equal to date diff on min and max dates . Even Month-year of date table should work
AverageReturn =
var _maxdate = calculate(MAX(Data[Date]),ALLSELECTED())
var _mindate = calculate(min(Data[Date]),ALLSELECTED())
var _diff = datediff(mindate,maxdate,month)
return
averagex(filter(SUMMARIZE(Data, Data[PorfolioId], "toAverage", [Return],"_dist" ,distinctcount(data[Month-year])),[_dist]=_diff),[Return])
Hi Amit,
Thank you for the solution. This worked perfectly for us.
Regards
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 |
---|---|
107 | |
99 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |