Hi ,
In the Mini Series - Budgeting and Forecasting --> 3rd item
'Budgeting Examples in Power BI wDax.pbix is a measure which is not totally correct.
Days in Year =
CALCULATE ( COUNTROWS ( Dates ) ,
ALLEXCEPT ( Dates, Dates[Year] ) )
// Will only return the dates in a year if a single year is in the filter context
The original pbix has a filter on the Visual restricting the year to 2016
I updated this file to use a more recent version of the Source Data with different years and saw that this measure then returns the Count of all rows for all years
Unless these is a Filter on the Visual it will return incorrect values when
a. Multiple years are selected in a Slicer
b. No year is Selected in the Slicer
The following measure will always return a correct value based on the Filter Context
Days in Filter Context Year =
CALCULATE ( COUNTROWS ( Dates ) ,
ALL ( Dates[Date] ),
VALUES ( Dates[Year])
// will always return the number of days in a Year based on the Year Filter context
By placing a Slicer on Year , even if No Year is selected in the Slicer the second measure will return the correct days in the Table for the Dates in context but the first will show the count of every row in the data set
[Updated - Budgeting Examples In Power BI wDAX.pbix (601.6 KB)
Regards
Russell
Hi Russell,
You have noticed an interesting difference.
The formula used in Days in Year, just counting the total days, regardless the years selected. is in my opinion more logic, and better understandable, hereby it is more likely that weird numbers are noticed and calculations, like average remain correctly calculated.
The outcome of the second formula suprises , why would the formula just only give the days of the last year, when nothing is selected ?
Kind regards,
Hi Deltaselect,
If there is no Filter on Dates[Year] either from a slicer or as a Visual Filter then Dates in Year will count every row in the Sales data because it assumes. it assumes there is one but there is not . . Similarly if 2 or 3 years are selected in a slicer the ALLEXCEPT will leave those Years on the Filters all all rows for all those years will be counted and in the table against every date you will see not the count of a single year but the total row count of however many years are selected. Effectively that is not the count of days in one given year
Regards
Russell
Personally i don’t like Visual filters as they hide things
If you mean the Filter pane, I agree with you. Filter panes can be overlooked by users, which can cause confusion. I try as much as possible to avoid them when building visuals. I do however like slicers
Hi @russellw999, did the response provided by @deltaselect help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!
Hi ,
It’s not really a problem it is something i observed which is interesting and i plan to look at in the rest of the Reports in Budgeting Reports when i get time
The measure Days in Year would be better named Days in All Selected Years as that is what it returns , an aggregation of the total days in all selected years
As a Year can have either 366 or 365 days then in the hypothetical situation you wanted to allocate a budget on a daily basis across any number of years selected then you would would want to use the code in ‘Days in Filter Context Year’ to get the correct denominator
Regards
Russell
Thanks for sharing this information and for starting this discussion @russellw999 . We appreciate users sharing varied updates and infos relevant to Power BI.
We are tagging this post as “Solved” due to the inactivity. For further questions related to this post, feel free to reopen this thread.