How to create a table with expected run dates?

Question

In power BI, I have a table named "Job Catalogue" with the following data:

Job ID	Name	Type	Frequency
1	Backup	Daily	2
2	Report	Weekly	3
3	Review	Monthly	15

Frequency represent the following:

If "Daily", is the number of times the task is performed during the day.

If "Weekly", is the day of the week the task is performed.

If "Monthly", is the day of the month the task is performed.

I also have a "Dates" table that is created dynamically with the next DAX formula: Dates = CALENDAR(StartDate, EndDate)

So if I create "Dates" table from January 1st to January 2nd (2024), the expected runs should be:

Job ID	Date
1	2024-01-01
1	2024-01-01
1	2024-01-02
1	2024-01-02
2	2024-01-02

Job id 1, appears 2 times each day.

Job id 2, appears 1 time because Jan 2nd is the 3th day of the week.

Job id 3, does not appears because there is no 15th day in "Dates" table, but if that date exists, it should be there.

Using DAX, how can i create the "Expected Runs" table?

I cannot use a "CROSSJOIN" because there is no relationship between "Dates" and "Job Catalogue".

Answer 1

We can use GENERATE to hydrate the Daily runs, and use CROSSJOIN (or GENERATE) to help with the Weekly & Monthly runs, then filter based on what is needed.

Expected Runs = 
  var tblDaily =
    CROSSJOIN(
      SELECTCOLUMNS(
        GENERATE(
          FILTER( 'Job Catalogue', [Type] = "Daily" ),
          GENERATESERIES( 1, [Frequency] )
        ), 
        "Job ID", [Job ID]
      ),
      ALL(Dates[Date])
    )

  var tblWeeklyMonthly = 
    SELECTCOLUMNS(
      FILTER(
        CROSSJOIN( ALL(Dates[Date]), FILTER( 'Job Catalogue', [Type] <> "Daily" ) ),
        ( [Type] = "Weekly" && WEEKDAY([Date], 1) = [Frequency] ) ||
        ( [Type] = "Monthly" && DAY([Date]) = [Frequency] )
      ),
      "Job ID", [Job ID],
      "Date", [Date]
    )

  return UNION( tblDaily, tblWeeklyMonthly )