topic Re: DAX to subract an amount for only one person? in DAX Commands and Tips

DAX to subract an amount for only one person?

gingerclaire — Mon, 14 Nov 2022 14:25:33 GMT

Hi,

I am working on a Power BI report to work out how many working days each person has in a month.

I have been able to get to the stage where I have subtracted weekend, sick leave, holiday and bank holidays - but I have one person who does not work mondays.

At the moment I am using the following measure to count total working days:

(A-B) Working days minus days out = ('Date table'[A - Total working days])-CALCULATE(SUM(Leave[B - Total working days out of action]))

I have a separate measure to count the number of Mondays - but I cannot figure out how to subtract the [No. of Mondays] from just her total? Please help!

I want to show in a table:

Name	Total working days in month
Person A	22
Person B (who doesn't work mondays	18

Re: DAX to subract an amount for only one person?

amitchandak — Tue, 15 Nov 2022 02:19:17 GMT

gingerclaire , for the workday of the month you can use networkdays and customize it as per each person

https://amitchandak.medium.com/power-bi-dax-function-networkdays-5c8e4aca38c

Re: DAX to subract an amount for only one person?

v-easonf-msft — Tue, 15 Nov 2022 06:20:38 GMT

Hi, gingerclaire

If you just want to subtract the [No. of Mondays] from just Person B total, please add a new measure like:

Result1 = IF ( MAX ( 'Table'[Name] ) = "Person B", // MAX ( 'Table'[Name] ) IN { "Person B", "Person xx" }, [Total working days in month], [Total working days in month] - [No. of Mondays] )

Best Regards,
Community Support Team _ Eason

Re: DAX to subract an amount for only one person?

gingerclaire — Tue, 15 Nov 2022 15:25:55 GMT

This worked - thank you so much! Only problem is I can't get the totals to show the 'correct' amount in the table (common measures issue but I don't quite understand how to fix it no matter how much time I spend reading about it!)