Forum Discussion
Frequency Calender
- 4 years ago
Hi crsjunior
Here's a solution that uses the GENERATE function to evaluate each day against each 'line item' to see if the combination of initial date and frequency is a match.
Here's what the test data looked like
Line Item Initial Date Frequency Item 1 01/08/2022 1 Item 2 07/08/2022 4 Item 3 06/08/2022 2 Item 4 16/08/2022 6 Item 5 13/08/2022 1 Plus I have a Date table in the model
Here's the measure to check if a line item occurs on a date. (to be more specific it counts the number of occurrences for all line items and dates in the filter context, giving you totals per line item and per date that make sense / may be useful)
Occurs on this date = VAR _Occurs = CALCULATETABLE( GENERATE( 'Date', FILTER('Line Items', MOD('Line Items'[Initial Date] - 'Date'[Date], 'Line Items'[Frequency]) = 0 && 'Date'[Date] >= 'Line Items'[Initial Date] ) ), CROSSFILTER('Date'[Date],'Line Items'[Initial Date], None) ) VAR _Result = COUNTROWS(_Occurs) RETURN _ResultIn a matrix visual that looks like this - notice the total row is giving number of occurances for all line items on that day
.. with background color and font color set to On
Hi crsjunior
Here's a solution that uses the GENERATE function to evaluate each day against each 'line item' to see if the combination of initial date and frequency is a match.
Here's what the test data looked like
| Line Item | Initial Date | Frequency |
| Item 1 | 01/08/2022 | 1 |
| Item 2 | 07/08/2022 | 4 |
| Item 3 | 06/08/2022 | 2 |
| Item 4 | 16/08/2022 | 6 |
| Item 5 | 13/08/2022 | 1 |
Plus I have a Date table in the model
Here's the measure to check if a line item occurs on a date. (to be more specific it counts the number of occurrences for all line items and dates in the filter context, giving you totals per line item and per date that make sense / may be useful)
Occurs on this date =
VAR _Occurs =
CALCULATETABLE(
GENERATE(
'Date',
FILTER('Line Items',
MOD('Line Items'[Initial Date] - 'Date'[Date], 'Line Items'[Frequency]) = 0
&& 'Date'[Date] >= 'Line Items'[Initial Date]
)
),
CROSSFILTER('Date'[Date],'Line Items'[Initial Date], None)
)
VAR _Result = COUNTROWS(_Occurs)
RETURN
_Result
In a matrix visual that looks like this - notice the total row is giving number of occurances for all line items on that day
.. with background color and font color set to On
- crsjunior4 years agoAdvocate I