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. 

 

Example: initial date: 01/08/2022, with frequency 7, it has to apear in the dates (08/08/2022; 15/08/2022; 22/08/2022......26/08/2022)

 

Below, image of what I need to replicate in Power BI.

 

Couldn't think on a solution with DAX. 

  • PaulOlding's avatar
    PaulOlding
    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

     

3 Replies

  • PaulOlding's avatar
    PaulOlding
    Solution Sage
    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