topic Re: Calculated Measure SUM in DAX Commands and Tips

Calculated Measure SUM

ferdri3 — Wed, 06 Jul 2022 11:40:33 GMT

Hi everyone,

I've been at this for quite some hours now, so I decided to see if there is some help out here haha.

I'm working on an HR dashboard and they requested to see some teams back in time.

To keep it simple, I have 5 columns: Index column, Full name, Join date, Leave date (blank if still active employees) and FTE.

In a seperate file I keep all dates (gregorian and fyscal).

To see a list (matrix) with people who worked in a team at a point in time I used the following calculated measure to see if they are "Working" or "Not Working" on the date selected in the slicer.

Working = VAR Selected_Date = MAX ( 'DateTable'[Gregorian Date] ) RETURN SWITCH ( TRUE (), MIN ( 'Consolidated'[Join Date] ) <= Selected_Date && MAX ( 'Consolidated'[Leave Date] ) >= Selected_Date, "Working", MIN ( 'Consolidated'[Join Date] ) <= Selected_Date && MAX ( 'Consolidated'[Leave Date] ) = BLANK (), "Working", "Not Working" )

...this part works as expected.

To give a single number in a card of the sum total, I use the following code:

Working Card = COUNTROWS(FILTER(DISTINCT('Consolidated'[Index]), [Working] = "Working"))

This works nicely as well.

The feedback I got was they would like to see the total of FTE (which can for example be 0,8 for some employees as well), not so much the amount of people (countrows).

Everything I try to sum up the FTE does not work and gives me errors as a calculated measure cannot be used in a true/false statement etc.

Anyone got any tips for this one?

Thanks a lot for you time and help!

Re: Calculated Measure SUM

tamerj1 — Wed, 06 Jul 2022 13:07:07 GMT

Hi ferdri3
I assume that you are slicing by date. then plesae try

Total FTE = SUMX ( VALUES ( 'DateTable'[Gregorian Date] ), CALCULATE ( VAR Selected_Date = MAX ( 'DateTable'[Gregorian Date] ) RETURN SWITCH ( TRUE (), MIN ( 'Consolidated'[Join Date] ) <= Selected_Date && MAX ( 'Consolidated'[Leave Date] ) >= Selected_Date, SUM ( 'Consolidated'[FTE] ), MIN ( 'Consolidated'[Join Date] ) <= Selected_Date && MAX ( 'Consolidated'[Leave Date] ) = BLANK (), SUM ( 'Consolidated'[FTE] ) ) ) )

Re: Calculated Measure SUM

Anonymous — Tue, 12 Jul 2022 13:59:35 GMT

Hi ferdri3 ,

I created some data:

Table：

Date:

Here are the steps you can follow：

1. Create measure.

Flag = var _select=SELECTEDVALUE('Date'[Date]) return IF( AND( _select >=MAX('Table'[Join date]) , _select <MAX('Table'[Leave date])) || AND( _select >=MAX('Table'[Join date]) , MAX('Table'[Leave date])=BLANK()) ,"Working", "Not Working")

Working Card = COUNTX( FILTER(ALL('Table'), [Flag]="Working"),[Full name])

2. Result:

If you need pbix, please click here.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly