cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
lachb82 Frequent Visitor
Frequent Visitor

actual or budget profit and loss

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 agrgument, but just can't seme 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],
BLANK())
 
Many thanks in advance...
2 REPLIES 2
kentyler Member
Member

Re: actual or budget profit and loss

Actual or Budget = IF(
SELECTEDVALUE('SlicerTable[Date]', 0)  = 'Dates'[Date]
'tablename'[Actual],
'tablename'[P&L])
 
I'm faking the table names here.
Selectedvalue will return the value selected in a slicer (or multiple values selected)
So this is trying to say if the selected month is equal to the month in the row, then return the actual, otherwise return the P&L
 
lachb82 Frequent Visitor
Frequent Visitor

Re: actual or budget profit and loss

thank you for the suggestion... though it's not giving the desired output

 

I get an error on the = 'Dates'[Date]
 
I have a Dates Table that defines financial years, months etc.
 
How do I get reference to the dates table...
 
Anyway the Matrix is still not filtering the values relative to the slicer selection
 
I have tried the below... but I can see I am refencing the same (moving) date based on the selected value...
 
Actual or Budget = IF(
SELECTEDVALUE(Dates[Date], 0 ) <= ENDOFMONTH(Dates[Date]),
CALCULATE( SUM('Invoices'[Line Amount Calculation]),
FILTER( 'Accounts', Accounts[Class] = "Revenue" || Accounts[Class] = "Expense" ))
 
- [P&L - Credit Notes] + [P&L (Journals)],
Blank())

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Top Kudoed Authors
Users Online
Currently online: 45 members 813 guests
Please welcome our newest community members: