Forum Discussion

android1's avatar
android1
Icon for Post Patron rankPost Patron
9 years ago

SUM values if date equal to

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")   b...
Need Help
android1's avatar
android1
Icon for Post Patron rankPost Patron
9 years ago

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's avatar
android1
Icon for Post Patron rankPost Patron
9 years ago

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

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    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