Actual v budget month by month 12 months

DAX DAX Calculations
1

Hi all

I am trying to create a monthly profit and loss report that displays both actual and budget relative to the date selected.

I can use the matrix visual and add measures for actual P&l and budget. That’s OK, however I would like to show only the actual relative to the month selected, and the remainder being budget.

I have tried a logical argument, but just can’t seem to get this right. Either it starts summing all of the actual months together, creates and error.

the ideal scenario would like something like…

Actual Actual Budget Budget Budget… etc for a financial year

Jul-19 Aug-19 Sep-19 Oct-19 Nov-19

Below is the DAX I have tried using but I can’t seem to filter the first few months to only show the actual relative to the month selected in a slicer.

Actual or Budget = IF(

LASTDATE(Dates[Date]) <= ENDOFMONTH( Dates[Date] ),

[P&L],

[Budget] )

Many thanks in advance…

2

Please review the following training modules to help solve your issue. If you still have issues after reviewing, please post a sample PBIX file so that we can better assist you with your question.

Thanks
Enterprise%20DNA%20Expert%20-%20Small

3

Yes highly recommend reviewing in details the above modules placed into the thread from Jarrett.

This will cover everything you need to know to achieve what you’re looking for.

There’s a bit to learn but very achievable.

Thanks
Sam

4

thank you for the replies… I have re-watched some of the training; I’m clearly missing something.

I feel it should be quite straight forward in comparing two values - one from a table of dates, and one from a slicer and if the one in the table is earlier than the on selected return an actual result.

I also have a budget outside of PBI already. these training videos seemed to focus more on creating the budget inside the model/report - which is great, but doesn’t suit what we currently have.

I have recently tired converting the dates to their serial number and comparing but still no result…

This is the latest DAX I have and the result

Actual Budget Filter =

var lastdayofmonth = value(Dates[end of Month])
var selecteddayofmonth = value(SELECTEDVALUE(Dates[Date]))

Return

IF(selecteddayofmonth = lastdayofmonth, "AWESOME", "Doesn't Work :/" )

Annotation%202019-11-26%20215211
Annotation 2019-11-26 215211.jpg1549×508 73.2 KB

In my simple Excel based mind this should be simple

Any thoughts would be greatly appreciated.

5

Please post a sample PBIX file so we can better assist you.

Thanks
Enterprise%20DNA%20Expert%20-%20Small

6

Thanks…

I have come up with something using a combination of LASTDATE and TODAY()

Actual / Budget filter = IF( LASTDATE( Dates[Date] ) <= today() ,
“Actual”, “Budget”)

This works nicely… Except
It is not responsive to month filters. I can work with this for now as long as the data is reviewed in the month preceding the reporting date.

I’ll try and get the pbix, but there are two files - one is the DM and one is the reports.

7

Hi @lach82, we’ve noticed that no further response has been received from you since November 28, 2019. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. You may reopen a new thread when the need arise.