Time period "yesterday" has an error (based on an article by Phil Seamark

[Dennis140684]

Hi there, I have based on the article, https://dax.tips/2019/10/09/row-based-time-intelligence/, Time periods created that I use as a filter. Unfortunately I get the following bug:
I set up the time period "yesterday". Always on the first of every month, this period always causes the "Time Periods" table to display an error. From the second of the month, the time periods will work properly again. How can I fix this error and what is the reason for it?
Attached is the Dax code I used with all the time periods set up and the PowerBI error message that was displayed.

VAR Today = MAX(DIM_Kalender[Date])

VAR ThisYear = YEAR(Today)

VAR ThisMonth = MONTH(Today)

VAR ThisDay = DAY(Today)

RETURN

    SELECTCOLUMNS(

        UNION

            (

                // Letzter Monat

                ADDCOLUMNS(

                GENERATE(

                    SELECTCOLUMNS({"Letzter Monat"},"Period",[Value]) ,

                    GENERATESERIES(

                        DATE(ThisYear , ThisMonth - 2 , 1) ,

                        Today

                    )

                ),"Axis Date",[Value]),

                // All Time

                 ADDCOLUMNS(

                    GENERATE(

                    SELECTCOLUMNS({"All Time"},"Period",[Value]) ,

                    GENERATESERIES(

                            DATE(ThisYear-20 , ThisMonth , ThisDay) ,

                            Today-1

                            )

                        ),

                        "Axis Date",[Value]),

                // Letzte 3 Monate

                ADDCOLUMNS(

                GENERATE(

                    SELECTCOLUMNS({"Letzte 3 Monate"},"Period",[Value]) ,

                    GENERATESERIES(

                        DATE(ThisYear , ThisMonth - 3 , 1) ,

                        Today

                    )

                ),"Axis Date",[Value]),

                // Dieses Jahr

                ADDCOLUMNS(

                GENERATE(

                    SELECTCOLUMNS({"Dieses Jahr"},"Period",[Value]) ,

                    GENERATESERIES(

                        DATE(ThisYear , 1 , 1) ,

                        Today

                    )

                ),"Axis Date",[Value]),

                // Letztes Jahr

                ADDCOLUMNS(

                GENERATE(

                    SELECTCOLUMNS({"Letztes Jahr"},"Period",[Value]) ,

                    GENERATESERIES(

                        DATE(ThisYear-1 , 1 , 1) ,

                        Date(ThisYear-1,12,31)

                    )

                ),"Axis Date",[Value]),

                // Heute

                ADDCOLUMNS(

                GENERATE(

                    SELECTCOLUMNS({"Heute"},"Period",[Value]) ,

                    GENERATESERIES(

                        DATE(ThisYear , ThisMonth , ThisDay) ,

                        Date(ThisYear,ThisMonth,ThisDay)

                    )

                ),"Axis Date",[Value]),

                // Gestern

                ADDCOLUMNS(

                    GENERATE(

                    SELECTCOLUMNS({"Gestern"},"Period",[Value]) ,

                    GENERATESERIES(

                            DATE(ThisYear , ThisMonth , ThisDay-1) ,

                            Date(ThisYear, ThisMonth,ThisDay-1)

                            )

                        ),

                        "Axis Date",[Value]

                    )

            ) ,  

        "Date" , [Value] ,

        "Period" , [Period] ,

        "Axis Date" , [Axis Date]

    )

[v-rzhou-msft · Accepted solution]

Hi @Dennis140684 ,

I create a sample to test  your code, and I could reproduce your error.

My Sample code:

Table = 
VAR Today = DATE(2022,03,01)

VAR ThisYear = YEAR(Today)

VAR ThisMonth = MONTH(Today)

VAR ThisDay = DAY(Today)
RETURN
GENERATESERIES(DATE(ThisYear,ThisMonth,ThisDay-1),TODAY() -1)

You see here I use 2022/03/01 as Today and this measure will return same error like yours.

This is because ThisDay =1, ThisDay-1=0, so Date(2022,03,0) will make code return error. I see many same parts as below in your code. ThisMonth -2/This Month -3 will return error if the month<=2/3. It is incorrect.

  GENERATESERIES(

                        DATE(ThisYear , ThisMonth - 2 , 1) ,

                        Today

                    )
...  
GENERATESERIES(

                        DATE(ThisYear , ThisMonth - 3 , 1) ,

                        Today

                    )
...
...
 GENERATESERIES(

                            DATE(ThisYear , ThisMonth , ThisDay-1) ,

                            Date(ThisYear, ThisMonth,ThisDay-1)

                            )

Try EOMONTH() Function to get previous date and use Today -1 directly instead of Date(...,ThisDay-1).

Table =
VAR Today =
    MAX ( DIM_Kalender[Date] )
VAR ThisYear =
    YEAR ( Today )
VAR ThisMonth =
    MONTH ( Today )
VAR ThisDay =
    DAY ( Today )
RETURN
    SELECTCOLUMNS (
        UNION (
            // Letzter Monat
            ADDCOLUMNS (
                GENERATE (
                    SELECTCOLUMNS ( { "Letzter Monat" }, "Period", [Value] ),
                    GENERATESERIES ( EOMONTH ( _Today, -3 ) + 1, Today )
                ),
                "Axis Date", [Value]
            ),
            // All Time
            ADDCOLUMNS (
                GENERATE (
                    SELECTCOLUMNS ( { "All Time" }, "Period", [Value] ),
                    GENERATESERIES ( DATE ( ThisYear - 20, ThisMonth, ThisDay ), Today - 1 )
                ),
                "Axis Date", [Value]
            ),
            // Letzte 3 Monate
            ADDCOLUMNS (
                GENERATE (
                    SELECTCOLUMNS ( { "Letzte 3 Monate" }, "Period", [Value] ),
                    GENERATESERIES ( EOMONTH ( _Today, -4 ) + 1, Today )
                ),
                "Axis Date", [Value]
            ),
            // Dieses Jahr
            ADDCOLUMNS (
                GENERATE (
                    SELECTCOLUMNS ( { "Dieses Jahr" }, "Period", [Value] ),
                    GENERATESERIES ( DATE ( ThisYear, 1, 1 ), Today )
                ),
                "Axis Date", [Value]
            ),
            // Letztes Jahr
            ADDCOLUMNS (
                GENERATE (
                    SELECTCOLUMNS ( { "Letztes Jahr" }, "Period", [Value] ),
                    GENERATESERIES ( DATE ( ThisYear - 1, 1, 1 ), DATE ( ThisYear - 1, 12, 31 ) )
                ),
                "Axis Date", [Value]
            ),
            // Heute
            ADDCOLUMNS (
                GENERATE (
                    SELECTCOLUMNS ( { "Heute" }, "Period", [Value] ),
                    GENERATESERIES (
                        DATE ( ThisYear, ThisMonth, ThisDay ),
                        DATE ( ThisYear, ThisMonth, ThisDay )
                    )
                ),
                "Axis Date", [Value]
            ),
            // Gestern
            ADDCOLUMNS (
                GENERATE (
                    SELECTCOLUMNS ( { "Gestern" }, "Period", [Value] ),
                    GENERATESERIES ( Today - 1, Today - 1 )
                ),
                "Axis Date", [Value]
            )
        ),
        "Date", [Value],
        "Period", [Period],
        "Axis Date", [Axis Date]
    )

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[tamerj1]

Hi @Dennis140684 

Try replacing the GENERATESERIES function with CALENDAR function