Forum Discussion
Helper IIBUG in Power bi
Dear friends,
i beleive there is a bug in power bi DATEADD(DimDate[Date],-1,MONTH)
it works in a very strange way, if i choose from 6/1 to 6/25 it returns the whole month of May,
but if i just choose from 6/1 to 6/24 it works okay
please see below
but now after i change the slicer to go back just one day :
i think the power bi treat 6/25 and the end of month
why this BUG is happening???!!
Hi Amrselim1989
It is not a bug, they just have designed it this way šThis is what the documentation says, and I quote:
"The result table has an entire month if an entire month is selected in the source table. The notion of āmonth selectedā is based on the dates available in the underlying date table. This way:- If the <Dates> argument includes all the days in April (30 rows), the result for the previous month includes all the days in March (31 days).
- If the <Dates> argument includes the first 15 days in April (15 rows), the result for the previous month includes the first 15 days in March (15 rows)."
Seems that the last date in your date table is TODAY ( ), therefore, it considers that complete month is included in the source table and thus returning the complete previous month. It is always recommended when using time intelligence functions, to include the complete dates of the years (from Jun 1st to Dec 31st) in order to avoid falling in such errors.
In my opinion, time intelligence functions are not user friendly as they were supposed to be and I personally don't use them.
3 Replies
- tamerj1
Community Champion
Hi Amrselim1989
It is not a bug, they just have designed it this way šThis is what the documentation says, and I quote:
"The result table has an entire month if an entire month is selected in the source table. The notion of āmonth selectedā is based on the dates available in the underlying date table. This way:- If the <Dates> argument includes all the days in April (30 rows), the result for the previous month includes all the days in March (31 days).
- If the <Dates> argument includes the first 15 days in April (15 rows), the result for the previous month includes the first 15 days in March (15 rows)."
Seems that the last date in your date table is TODAY ( ), therefore, it considers that complete month is included in the source table and thus returning the complete previous month. It is always recommended when using time intelligence functions, to include the complete dates of the years (from Jun 1st to Dec 31st) in order to avoid falling in such errors.
In my opinion, time intelligence functions are not user friendly as they were supposed to be and I personally don't use them.
Amrselim1989Oh My gosh, i spent days try to figure out what's going on, as you said the mistake was in M Query itself,
i created a DimDate in depends on the fact table with Min date and Max date there i avoid dates with blank in my report so user with never find a month date for example with no data,
it appears that M query treat MAx Date of Fact table as today() which it's so stupid,
to avoid this i first used
Date.EndOfYear(#"Calculated Earliest") but it return the whole year, then i used= Date.AddDays(List.Max(#"Removed Other Columns"[order_date]), 1)
just to add one more day after last date of data so it doesn't treat it as today() š¤£š¤£
i think this is stilll a bug š¤£š¤£
thanks you so much sir.- tamerj1
Either today or max of fact date will not have complete month for the last month. It is not a bug. If you supply the complete dates available in the date table for a particular month (example Jun 1 to June 25 are all the dates of the month June available in the date table) then DATEADD treats it as complete month and return a complete month as an output. Again it could be stupid but it's not a bug.