Forum Discussion
filtering date table
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
Hi,
Modify your measure to
Rolling4monthPromoters = CALCULATE([Promoters],DATESINPERIOD('Date'[Date],[maxresponsedate],-4,MONTH))Hope this helps.
2 Replies
try the following measure ;
Rolling4monthPromoters =var d = calculate(max('Date'[Date]),survey)returnCALCULATE([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.Hi,
Modify your measure to
Rolling4monthPromoters = CALCULATE([Promoters],DATESINPERIOD('Date'[Date],[maxresponsedate],-4,MONTH))Hope this helps.
