SUM values if date equal to

[android1]

Hi,

Trying to SUM the values in column Num Hrs if iDate = 17 Marach 2017.

Have this so far -> Bank Hol = CALCULATE(SUM(CarerActualWork[Num Hrs]),CarerActualWork[iDate]= "17 March 2017")

but not quite right.

Gerry

[Phil_Seamark]

Hi @android1

Please try this slight variation

Bank Hol = CALCULATE(
			SUM(CarerActualWork[Num Hrs]),
			CarerActualWork[iDate]= DATE(2017,3,17)
			)

[android1]

Hi Phil,

Hi Phil, Thanks for that. For some reason it's summing for all dates.

[Phil_Seamark]

Hi @android1

Any chance you can mock up your expected result for that dataset?

Cheers,

Phil

[android1]

[v-shex-msft]

Hi @android1,

According to your description, you want to calculate the running total, right?
If this is a case, you can try to use below formula.

Bank Hol = CALCULATE(
			SUM(CarerActualWork[Num Hrs]),
			CarerActualWork[iDate] <= MAX(CarerActualWork[Date])
			)

Regards,

Xiaoxin Sheng

[android1]

Thanks for the reply,

What I want is to show Num Hrs as a new column called Bank Hol & only display Num Hrs for the date 17th March 2017 which is a Bank Holiday. This column should only contain Num Hrs if Num Hrs are there for this date only.

Gerry

[android1]

Hi,

What in this measure could possibly mean that Bank Hol are shown for dates other than 17th March 2017? 

Makes no sense to me.

Bank Hol = CALCULATE(SUM(CarerActualWork[Num Hrs]),CarerActualWork[iDate] = DATE(2017,03,17))

[v-shex-msft]

Hi @android1,

If you want to get the sum of the data when date is equall to 03/17/2017, You can use all function to ignore the original filter:

Bank Hol = CALCULATE(SUM(CarerActualWork[Num Hrs]),FILTER(ALL(CarerActualWork),CarerActualWork[iDate] = DATE(2017,03,17)))

Sum of values which has the same date:

Bank Hol = 
SUMX(FILTER(ALL(CarerActualWork),CarerActualWork[iDate] = MAX(CarerActualWork[iDate])),CarerActualWork[Num Hrs])

Regards,

Xiaoxin Sheng