Forum Discussion

crsjunior's avatar
crsjunior
Advocate I
4 years ago
Solved

Frequency Calender

Hello,    In my database there's a column with the inicial date and a number column with the frequency.   What I need is to create a visual showing when that data will apear in the next 60 days. ...
  • PaulOlding's avatar
    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
        _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