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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.