Matrix: If statement return column

[HenryJS]

Hi all,

I have the  two tables 'Placements (2)' and a date table visualised in the below matrix with the measure:

Timesheet Y/N = IF(SELECTEDVALUE('Placements (2)'[PlacementStartDate])<= MAX('DaysAndWeeks'[Date]) && SELECTEDVALUE('Placements (2)'[PlacementEndDate]) >= MAX('DaysAndWeeks'[Date]), "Yes","")

Instead of the "Yes" is it possible to RETURN the 'Placements(2)'[PayRate] Column?

This would then visualise the figure instead of the "Yes" and would be totalled at the end of the columns.

Placements (2) Table:

CandidateName	PlacementStartDate	PlacementEndDate	Rate1PayRate	Rate1ChargeRate
Jayden	06/07/2020 01:00	29/11/2020 00:00	21.17	31.73
Ian	01/07/2020 01:00	30/09/2020 01:00	30	33.6
Michael	07/04/2020 01:00
Tim	07/04/2020 01:00	03/05/2020 01:00	21	25
Nicolae	06/04/2020 01:00	03/05/2020 01:00	17	18.87
Marius	06/04/2020 01:00	03/05/2020 01:00	15	16.65
Max	01/04/2020 01:00	27/11/2020 00:00	323.75	365.84

[amitchandak · Accepted solution]

@HenryJS ,

Try like

IF(SELECTEDVALUE('Placements (2)'[PlacementStartDate])<= MAX('DaysAndWeeks'[Date]) && SELECTEDVALUE('Placements (2)'[PlacementEndDate]) >= MAX('DaysAndWeeks'[Date]), max('Placements(2)'[PayRate] ),blank())

[HenryJS]

@amitchandak thank you

The Total on the right hand side isn't functioning?

Also it seems to have filtered down the 'CandidateName' to a lesser amount. Is this because if there is no 'Pay Rate' they won't be shown?

Cheers

[amitchandak]

@HenryJS ,

Please find the attached file after signature

[HenryJS]

Hi @amitchandak 

Thanks for your help so far - that's worked.

I am now having issues with the 'Total' on the rows.

It is not Totalling correctly where I have put two x's.

The grand total is maxxing out at 1,400,000 and the sub totals are only showing max not the actualy total.

Can privately share data if you would like?

Thanks