Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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]
)
Solved! Go to 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.
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.
Try replacing the GENERATESERIES function with CALENDAR function
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
58 | |
50 | |
44 | |
19 | |
19 |