Forum Discussion
metric by date range with multiple sequential observations per category
Greg_Deckler I think I got it..
% Mkt Occ = VAR tmpunitStatus = ADDCOLUMNS(unitstatus,"Effective Date",IF(ISBLANK([End]),TODAY(),[End])) VAR tmpTable = FILTER( GENERATE( tmpunitStatus, CalendarTable ), and( And([Date] <= [End], [Date] >= [Start]),[Status]="Occupied") ) RETURN COUNTROWS(tmpTable)/DISTINCTCOUNT([UnitID])I realized part of the issue was that the fact table was linked to the date table, which was forcing unwanted behavior. When I plot this by day, it produces expected results. However, if you try summarize the data by any other time dimension (particularly because it is rows of days that is used in the generate function) then I get sums of daily amounts (of course) which I don't want. Therefore, inspired by this I came up with the following solution:
% Mkt Occ (Current) = VAR tmpTable = FILTER( unitstatus, and( And(max(CalendarTable[Date]) <= [End], max(CalendarTable[Date]) >= [Start]),[Status]="Occupied") ) RETURN COUNTROWS(tmpTable)/DISTINCTCOUNT(unitstatus[UnitID])Inspired by your suggested solution, dynamically filter the fact table based on the max date of the calendar table, which itself is dynamic based on the time dimension being plotted.
Greg_Deckler thanks for your reply! I think you are on to something. Using the periodic billing file as an example, the complexity I have is that "Customer A" is repeated, and I only want to count some of their orders, and not others.
nmamm Can you provide sample data to work with?
Where Start and End is equivalent to the example columns begin date and until date
UnitID Status Occured Start End 1 Occupied 12/1/2021 12/1/2021 11/30/2022 2 Occupied 12/1/2021 12/1/2021 11/30/2022 3 Occupied 12/1/2021 12/1/2021 11/30/2022 4 Occupied 12/1/2021 12/1/2021 11/30/2022 1 Occupied 12/1/2022 12/1/2022 2 Occupied 12/1/2022 12/1/2022 3 Occupied 12/1/2022 12/1/2022 4 Occupied 12/1/2022 12/1/2022 5 Occupied 4/1/2021 4/1/2021 3/31/2022 5 Occupied 4/1/2022 4/1/2022 12/31/2022 5 Occupied - On Notice 1/1/2023 1/1/2023 Greg_Deckler I think I got it..
% Mkt Occ = VAR tmpunitStatus = ADDCOLUMNS(unitstatus,"Effective Date",IF(ISBLANK([End]),TODAY(),[End])) VAR tmpTable = FILTER( GENERATE( tmpunitStatus, CalendarTable ), and( And([Date] <= [End], [Date] >= [Start]),[Status]="Occupied") ) RETURN COUNTROWS(tmpTable)/DISTINCTCOUNT([UnitID])I realized part of the issue was that the fact table was linked to the date table, which was forcing unwanted behavior. When I plot this by day, it produces expected results. However, if you try summarize the data by any other time dimension (particularly because it is rows of days that is used in the generate function) then I get sums of daily amounts (of course) which I don't want. Therefore, inspired by this I came up with the following solution:
% Mkt Occ (Current) = VAR tmpTable = FILTER( unitstatus, and( And(max(CalendarTable[Date]) <= [End], max(CalendarTable[Date]) >= [Start]),[Status]="Occupied") ) RETURN COUNTROWS(tmpTable)/DISTINCTCOUNT(unitstatus[UnitID])Inspired by your suggested solution, dynamically filter the fact table based on the max date of the calendar table, which itself is dynamic based on the time dimension being plotted.
nmamm Can you provide sample data to work with?
