I need some way to filter out dates from a date dimension, and consequently items in a fact table connected to that date dimension, based on a calculated date-span. The user will select one or more periods (the periods are predefined since they are timeperiods when a certain condition was applied to the facts in the fact table) (the user will select for instance 2018-11-01 to 2018-11-04 and 2018-11-13 to 2018-11-18 retrieved from two columns in the filter table (FromDate and ToDate) and then I want to compare the number of items in the fact table during (2018-11-01 to 2018-11-18 (19 days)) to the period before that (2018-10-13 to 2018-10-31 (also 19 days)).
I can create measures that calculates the start and end-dates for both periods and then create a measure that counts the number of facts in each period and slice that in a graph based on Year/Month/Date but i need to show every date from each period in the graph. Since the measure i calculated only counts occurances in the fact table I dont get any matches on dates where there where no facts. I believe I need to create a dynamic DateDimension that would contain the dates for the first selected periods and then another date-dimension that would contain the dates from the period before and then use those dimensions in my graph. Is it possible to create a dynamic table based on a calculated measure like this and if so, how could I do that?
Actually... what I actully want to do is to compare only the four days in the selected period to the matching four days in the previous period and the next six days in the last period and so on if the user has selected more then two periods, but that is the next step... If I can figure out how to do the first filtering described above I hope I can use that solution to do this second filtering.
Thanks in advance!
