Unable to use Date Slicer in measure effectively.

[birudvora]

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: 

maxdate = calculate(MAX(Data[Date]),ALLSELECTED())

mindate = calculate(min(Data[Date]),ALLSELECTED())

nMonths = CountRows('Data') This correctly calculates number months for which data is available

FilteredNMonths =

(Year([maxdate]) - Year([mindate]))*12 + MONTH([maxdate]) - Month([mindate])+1

This correctly calculates number of months Date Filter has selected.

Return = PRODUCT(Data[% Return])

AverageReturn = 

averagex(filter(SUMMARIZE(Data, Data[PorfolioId], "toAverage", [Return]),[nMonths]=[FilteredNMonths]),[Return])

Many Thanks for looking into this.

-

Birud

[amitchandak · Accepted 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])

[birudvora]

---

@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