Forum Discussion

ArchStanton's avatar
ArchStanton
Icon for Power Participant rankPower Participant
4 years ago
Solved

SUMX Formula creating a negative value

Hi,

 

The following formula produces a figure of -23 days which is incorrect, it should be 23 days.

 

Working Days = SUMX(FILTER(Deferrals,[regardingobjectid]= 'Cases'[incidentid]),Deferrals[Time in Deferral AP]) - 'Cases'[Duration from Assigned (wd)]

 

Duration from Assigned (wd) = 44

SUMX of Deferrals[Time in Deferral AP] = 21

 

I've tried swapping parts of the DAX around without success. 

Any ideas what I could do? 

 

Thanks

A

Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    4 years ago

    ArchStanton I'm thinking:

    Working Days = SUMX(FILTER(Deferrals,[regardingobjectid]= 'Cases'[incidentid]),'Cases'[Duration from Assigned (wd)] - Deferrals[Time in Deferral AP])

    or:

    Working Days = 'Cases'[Duration from Assigned (wd)] - SUMX(FILTER(Deferrals,[regardingobjectid]= 'Cases'[incidentid]),Deferrals[Time in Deferral AP])

     

2 Replies

  • ArchStanton's avatar
    ArchStanton
    Icon for Power Participant rankPower Participant
    4 years ago

    Thanks, the first option gave me an answer of 67 days which is wrong but the second formula was correct at 23 days.

    Many thanks!

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    4 years ago

    ArchStanton I'm thinking:

    Working Days = SUMX(FILTER(Deferrals,[regardingobjectid]= 'Cases'[incidentid]),'Cases'[Duration from Assigned (wd)] - Deferrals[Time in Deferral AP])

    or:

    Working Days = 'Cases'[Duration from Assigned (wd)] - SUMX(FILTER(Deferrals,[regardingobjectid]= 'Cases'[incidentid]),Deferrals[Time in Deferral AP])