cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Dennis140684
New Member

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

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.

Unbenannt.JPG

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]

    )

1 ACCEPTED SOLUTION
RicoZhou
Community Support
Community Support

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.

RicoZhou_0-1649143647577.png

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.

 

View solution in original post

2 REPLIES 2
RicoZhou
Community Support
Community Support

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.

RicoZhou_0-1649143647577.png

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
Super User
Super User

Hi @Dennis140684 

Try replacing the GENERATESERIES function with CALENDAR function

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors