Forum Discussion
Holiday Calculation: From Start and End Date to "Aggregation Graph"
Hi Anonymous !
It seems we can achieve this by just slightly modifying OwenAuger's solution here :smileyhappy:
http://community.powerbi.com/t5/Desktop/Generate-a-schedule-table/m-p/38834#M14436
Holiday Schedule = SUMMARIZE ( GENERATE ( HolidayTable, CALCULATETABLE ( VALUES ( 'Calendar'[Date] ), DATESBETWEEN ( 'Calendar'[Date], HolidayTable[Start Date], HolidayTable[End Date] ) ) ), HolidayTable[Name], 'Calendar'[Date], HolidayTable[Qualification] )Here's the result...
and the slicers you mentioned you'd like to use seem to work as well
Hope this helps and works in your case! :smileyhappy:
EDIT: The Qualification Slicer was not included in the previous pictures :smileyhappy:
Alternatively, here's my previous method. This was done without creating a new transformed table. Just a measure. Well, a measure and a date table...
I had a table where each record had a PersonID, a start date, and an end date. I had a date table with no active relationship to the other table. Having no relationship is important. Then I created a measure to count based on start and end dates. Put on a visual with some aggregation of a period dates (i.e. week, month, etc), this would give a count of active records during that time period.
Active Count = CALCULATE(
DISTINCTCOUNT(Table[PersonID]),
FILTER(
Table,
Table[Start Date] <= LASTDATE(DateTable[Date]) &&
Table[EndDate] >= FIRSTDATE(DateTable[Date])
)
)
The FIRSTDATE/LASTDATE thing always feels a little counterintuitive to me. The start and end look backward, but think of it this way: if they started anytime before the end of the period, then they were active during the period. Also if they ended anytime after the beginning of the period (whether that's during the period or after the end of the period), they were still active during the period. Make sense?
Also, Anonymous Dax-Experts: Daxperts?
Community ChampionHi Anonymous !
It seems we can achieve this by just slightly modifying OwenAuger's solution here :smileyhappy:
http://community.powerbi.com/t5/Desktop/Generate-a-schedule-table/m-p/38834#M14436
Holiday Schedule =
SUMMARIZE (
GENERATE (
HolidayTable,
CALCULATETABLE (
VALUES ( 'Calendar'[Date] ),
DATESBETWEEN (
'Calendar'[Date],
HolidayTable[Start Date],
HolidayTable[End Date]
)
)
),
HolidayTable[Name],
'Calendar'[Date],
HolidayTable[Qualification]
)
Here's the result...
and the slicers you mentioned you'd like to use seem to work as well
Hope this helps and works in your case! :smileyhappy:
EDIT: The Qualification Slicer was not included in the previous pictures :smileyhappy:
Alternatively, here's my previous method. This was done without creating a new transformed table. Just a measure. Well, a measure and a date table...
I had a table where each record had a PersonID, a start date, and an end date. I had a date table with no active relationship to the other table. Having no relationship is important. Then I created a measure to count based on start and end dates. Put on a visual with some aggregation of a period dates (i.e. week, month, etc), this would give a count of active records during that time period.
Active Count = CALCULATE(
DISTINCTCOUNT(Table[PersonID]),
FILTER(
Table,
Table[Start Date] <= LASTDATE(DateTable[Date]) &&
Table[EndDate] >= FIRSTDATE(DateTable[Date])
)
)
The FIRSTDATE/LASTDATE thing always feels a little counterintuitive to me. The start and end look backward, but think of it this way: if they started anytime before the end of the period, then they were active during the period. Also if they ended anytime after the beginning of the period (whether that's during the period or after the end of the period), they were still active during the period. Make sense?
Also, Anonymous Dax-Experts: Daxperts?
- Sean
Anonymous go with Anonymous's solution - seems more efficient - no need to create an extra table!
Since the other thread was my question I guess I should clarify why I asked for that table when I already had the Active Count formula in hand. It might help readers understand which use case calls for which solution, because both solutions technically work. Basically it's because Active Count retains that two-date structure and can't be related to a date table. If all you want is the count and maybe a few simple derivitives of it (average Active Count per week maybe) it's no big deal. But the more time intelligence stuff you want to do alongside it, the harder it becomes (compare average Active Count per week in the last six months with the same period last year. When you can write that formula, Grasshopper, you may leave the temple). My specific case was essentially predicted future payroll. I could do it without the schedule table, but with the table I can write very simple sum and count measures for things like total hours per week, instead of accomplishing the same with giant mutant versions of Active Count.
TL;DR: if you just want that count based on start and end dates, use the Active Count formula pattern. If you want anything too much more complicated than that, you may want to consider the schedule table.
this solution only works if you deactivate the relationship