actual or budget profit and loss

[lachb82]

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...

[kentyler]

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]

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())