Frequency Calender

[crsjunior]

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 · Accepted solution]

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

[crsjunior]

Hey @PaulOlding ! 

It worked perfectly with my dataset. 

Thank you

[latimeria]

Hi @crsjunior ,

You need to generate the dates in a (temp) table.

You can use generate()  GENERATE – DAX Guide