filtering date table

[powerbiexpert22]

i am using three tables customer , survey and date (calendar)

i am trying to calculate rolling promoters for last 4 months

i am using country as slicer

i want to show rolling 4 month based on last_response_date so i have joined date table with survey table based on last_response_date

i used below rolling 4 months formula  which is giving me incorrect result for "Germany" country since max('Date'[Date]) is used instead of 'survey'[last_response_date])

max of date is 01st Aug 2024 and max of last response date is 28/07/2024

i can easily replace max('Date'[Date]) with max ('survey'[last_response_date]) to get correct results however if i do this then what is the use of date table if i cannot use it in rolling calculations?

can it be possible to get correct results using date column  and without enabling bi directional filtering?

pbix file is available for refrerence: 

https://drive.google.com/file/d/1_51eQFFIeHCuzO8UfrvxXDVfH71JP3g9/view?usp=drive_link

Rolling4monthPromoters =

CALCULATE([Promoters],

DATESINPERIOD('Date'[Date],max('Date'[Date]),-4,MONTH))

[Ashish_Mathur · Accepted solution]

Hi,

Modify your measure to

Rolling4monthPromoters = CALCULATE([Promoters],DATESINPERIOD('Date'[Date],[maxresponsedate],-4,MONTH))

Hope this helps.

[Daniel29195]

@powerbiexpert22 

try the following measure ; 

Rolling4monthPromoters =

var d = calculate(max('Date'[Date]),survey)

return

CALCULATE([Promoters],

DATESINPERIOD('Date'[Date],d,-4,MONTH))


the idea is, that you want to max date for germany,. 

howver customer doesnt filter date . 

so when you are filtering country =  germany. 

and then in your code you are selecting max(date[date]) , you are returning the max date of dimdate independent of the filter on the country. 

let me know if this helps.