Days in Date Context for FY_Month Budgets

Power BI
1

Hi there, this is my first post in the forum. Will try to be brief, thanks!

I have monthly budgets which I would like to allocate per day.

To do so, I calculate the days in date context, no problem with that.

Problem comes when I try to calculate the days for the budget context, which is FY_Month.

I tried to replicate this from the “introduction to budgeting course”.

image

Therefore, the logic I have used is:
Days in FY_Month = CALCULATE(COUNTROWS(Dates),

ALLEXCEPT(Dates, Dates[FY_Month]))

Having done that, i get all the rows in the date table. 1856, as if the filter in allexcept had not applied.

Thanks in advance for your help.

2

Hi @JOSERPIVIDAL, we aim to consistently improve the topics being posted on the forum to help you in getting a strong solution faster. While waiting for a response, here are some tips so you can get the most out of the forum and other Enterprise DNA resources.

  • Use the forum search to discover if your query has been asked before by another member.

  • When posting a topic with formula make sure that it is correctly formatted to preformated text </>.

  • Use the proper category that best describes your topic

  • Provide as much context to a question as possible.

  • Include demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

I also suggest that you check the forum guideline https://forum.enterprisedna.co/t/how-to-use-the-enterprise-dna-support-forum/3951. Not adhering to it may sometimes cause delay in getting an answer.

3

Hi @JOSERPIVIDAL

AllExcept measure works by removing all filters except specified column in the current context. If FY_Month is not present as part of the Visual or is not applied as any other Filter like Slicer/Page/Report etc it will not work.

In Budgeting video, a Year filter was applied on Page Level that’s why AllExcept was working. For your case, try something like below.

Days in FY_Month =
CALCULATE (
    COUNTROWS ( Dates ),
    ALL ( Dates ),
    SUMMARIZE ( Dates, Dates[FY_Month], Dates[Fiscal Year] )
)

Days in FY_Month =
CALCULATE ( COUNTROWS ( Dates ), ALL ( Dates ), VALUES ( Dates[FY_Month] ) )

Check if this blog is helpful in understanding the difference.

Thanks
Ankit J

2 Likes
4
  • Hi @JOSERPIVIDAL, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Thanks!
5

It works perfectly fine now. Many thanks!