topic Re: Frequency Calender in DAX Commands and Tips

Frequency Calender

crsjunior — Mon, 08 Aug 2022 09:31:26 GMT

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.

Re: Frequency Calender

latimeria — Mon, 08 Aug 2022 10:15:08 GMT

Hi crsjunior ,

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

You can use generate() GENERATE – DAX Guide

Re: Frequency Calender

PaulOlding — Mon, 08 Aug 2022 10:22:19 GMT

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

Re: Frequency Calender

crsjunior — Mon, 08 Aug 2022 12:10:54 GMT

Hey PaulOlding !

It worked perfectly with my dataset.

Thank you