GENERATESERIES() from start date to end date with an interval of 15th day of the month

[brwalker]

Hello,

I'm calculating a DAX table to forecast revenue by using GENERATESERIES(start_date, end_date).  Rather than generating a series of dates between the start and end date, is it possible to GENERATESERIES() with a filter to show only a particular day of each month, resulting in a series of dates, one entry for each month?  I've tried to use intervals, such as 30, but months are skipped or duplicated.  

[Greg_Deckler]

@brwalker So like:

Table = 
  FILTER(
    GENERATESERIES(DATE(2023,1,1),DATE(2023,12,31),1),
    DAY([Value]) = 15
  )

[brwalker]

Thank you @Greg_Deckler  for the quick reply.  I've tried it, but the date inputs are dynamic.  Example code:

BACKLOG_v7_Revenue_TEST =

VAR baseTable =

    CALCULATETABLE (

        SUMMARIZECOLUMNS (

            Dim_PROJECT[Project_Name],

            -- Dim_PROJECT[Name],

            Dim_PROJECT[Dim_ORG.ORG],

            Dim_PROJECT[Dim_CONTRACT_ID.Prime_Contract_ID],

            Dim_PROJECT[Dim_POP.PoP Start],

            Dim_PROJECT[Dim_POP.PoP End],

            REVENUE[Last_Revenue_Date]

        ),

        Dim_PROJECT[Dim_POP.PoP Start] <> BLANK (),

        -- cannot generate series with empty values

        Dim_PROJECT[Dim_POP.PoP End]

            <> BLANK () --,  -- cannot generate series with empty values

    )

RETURN

    GENERATE (

        baseTable,

        VAR PoP_Days =

            DATEDIFF (

                Dim_PROJECT[Dim_POP.PoP Start],

                EOMONTH ( REVENUE[Last_Revenue_Date], 0 ),

                DAY

            )

        VAR _Start =

            CALCULATE (

                MINX ( VALUES ( Dim_PROJECT[ID] ), MIN ( Dim_PROJECT[Dim_POP.PoP Start] ) )

            )

        VAR _End =

            EOMONTH ( REVENUE[Last_Revenue_Date], 0 )

        VAR CTD_Revenue = [Revenue (Contract to Date)]

        VAR Contract_Value = [Project Value Total Amount]

        VAR _Value_Revenue =

            DIVIDE ( CTD_Revenue, PoP_Days, BLANK () )

        VAR _Value_Contract =

            DIVIDE ( Contract_Value, PoP_Days, BLANK () )

        VAR Billed = [Amount Billed]

        VAR _Value_Billed =

            DIVIDE ( Billed, PoP_Days, BLANK () )

        VAR _Backlog = _Value_Contract - _Value_Revenue

        RETURN

            GENERATE (

                GENERATESERIES ( _Start + 1, _End ),

                VAR inc = [Value]

                RETURN

                    ROW (

                        "Days", PoP_Days,

                        "Amount", _Value_Revenue,

                        "Last Date to Calc Backlog", EOMONTH ( REVENUE[Last_Revenue_Date], 0 ),

                        "Type", "Revenue"

                    )

            )

    )

[Greg_Deckler · Accepted solution]

@brwalker OK, so why not:

BACKLOG_v7_Revenue_TEST =
VAR baseTable =
    CALCULATETABLE (
        SUMMARIZECOLUMNS (
            Dim_PROJECT[Project_Name],
            -- Dim_PROJECT[Name],
            Dim_PROJECT[Dim_ORG.ORG],
            Dim_PROJECT[Dim_CONTRACT_ID.Prime_Contract_ID],
            Dim_PROJECT[Dim_POP.PoP Start],
            Dim_PROJECT[Dim_POP.PoP End],
            REVENUE[Last_Revenue_Date]
        ),
        Dim_PROJECT[Dim_POP.PoP Start] <> BLANK (),
        -- cannot generate series with empty values
        Dim_PROJECT[Dim_POP.PoP End]
            <> BLANK () --,  -- cannot generate series with empty values
    )
RETURN
    GENERATE (
        baseTable,
        VAR PoP_Days =
            DATEDIFF (
                Dim_PROJECT[Dim_POP.PoP Start],
                EOMONTH ( REVENUE[Last_Revenue_Date], 0 ),
                DAY
            )
        VAR _Start =
            CALCULATE (
                MINX ( VALUES ( Dim_PROJECT[ID] ), MIN ( Dim_PROJECT[Dim_POP.PoP Start] ) )
            )
        VAR _End =
            EOMONTH ( REVENUE[Last_Revenue_Date], 0 )
        VAR CTD_Revenue = [Revenue (Contract to Date)]
        VAR Contract_Value = [Project Value Total Amount]
        VAR _Value_Revenue =
            DIVIDE ( CTD_Revenue, PoP_Days, BLANK () )
        VAR _Value_Contract =
            DIVIDE ( Contract_Value, PoP_Days, BLANK () )
        VAR Billed = [Amount Billed]
        VAR _Value_Billed =
            DIVIDE ( Billed, PoP_Days, BLANK () )
        VAR _Backlog = _Value_Contract - _Value_Revenue
        RETURN
            GENERATE (
                FILTER(
                  GENERATESERIES ( _Start + 1, _End ),
                  DAY([Value]) = 15
                ),
                VAR inc = [Value]

                RETURN
                    ROW (
                        "Days", PoP_Days,
                        "Amount", _Value_Revenue,
                        "Last Date to Calc Backlog", EOMONTH ( REVENUE[Last_Revenue_Date], 0 ),
                        "Type", "Revenue"
                    )
            )
    )