In my Data warehouse, there are Dim date and Dim City and a Fact table which at the end of each month logs the population of each city, I have a problem to calculate max population of each city in a specific date range, should I use MDX query?
Using simple query in an OLTP I can easily group by city and select max population in a specific date range but in OLAP how can visually show max population in a specific date range (date range would be selected by user in run time as a filter).
As an example if City X has population of 100,000 in Jan and Feb and Population of 200,000 in March, If end user select population of City X in first 3 months, I have to extract max of population in first 3 months which is 200,000 but if user select first 2 months I have to show 100,000 as population. the last record in fact table doesn't necessarily have the latest data, so I have to extract max population in a given period of time.
I am using Power BI and a slicer filter for a specific date range, so I prefer don't write MDX or any SQL query.