Sum based on criteria on one column and exclude date from another column

[MosesDhas]

hi, am a newbie here and looking for support on a calculation...

Scenarios: i need to sum the dataset with the following:

There are many transaction types. all transaction types except for "IWO_WH_PRG" should be summed based on the Effective date drop down filter on the report.

For the "IWO_WH_PRG" transaction type, the sum should ignore the effective date

Data set

Transaction Type	Contract	Entity	Job Number	Sub-function	Activity Code	Unit	Quantity	Man Hours	Effective Date	Revision
IAFC_ERN_PRDC	D7018	923	60158	FA101	B100120	HR		1416.92	23-Dec-20	100
IAFC_ERN_PRDC	D7018	923	60158	FA101	B100135	HR		1976.29	23-Dec-20	100
IAFC_ERN_PRDC	D7018	923	60158	FA101	B100730	HR		316.06	23-Dec-20	100
IAFC_ERN_PRDC	D7018	923	60158	FA101	B100830	HR		15.01	23-Dec-20	100
IAFC_ERN_PRDC	D7018	923	60158	FA101	B100930	HR		99.58	23-Dec-20	100
IAFC_ERN_PRDC	D7018	923	60158	FA101	B101005	HR		9.04	23-Dec-20	100
IAFC_ERN_PRDC	D7018	923	60158	FA101	B101006	HR		1414.69	30-Dec-20	100
IAFC_ERN_PRDC	D7018	923	60158	FA101	B101007	HR		322.34	30-Dec-20	100
IAFC_ERN_PRDC	D7018	923	60158	FA101	B101305	HR		0	30-Dec-20	100
IAFC_ERN_PRDC	D7018	923	60158	FA101	B101306	HR		738.35	30-Dec-20	100
IAFC_ERN_PRDC	D7018	923	60158	FA101	B101307	HR		200	30-Dec-20	100
IWA_WH_PRG	D7018	923	60158	FA101	B100120	HR		1416.92	23-Dec-20	100
IWA_WH_PRG	D7018	923	60158	FA101	B100135	HR		1976.29	23-Dec-20	100
IWA_WH_PRG	D7018	923	60158	FA101	B100730	HR		316.06	23-Dec-20	100
IWA_WH_PRG	D7018	923	60158	FA101	B100830	HR		15.01	23-Dec-20	100
IWA_WH_PRG	D7018	923	60158	FA101	B100930	HR		99.58	23-Dec-20	100
IWA_WH_PRG	D7018	923	60158	FA101	B101005	HR		9.04	23-Dec-20	100
IWA_WH_PRG	D7018	923	60158	FA101	B101006	HR		1414.69	30-Dec-20	100
IWA_WH_PRG	D7018	923	60158	FA101	B101007	HR		322.34	30-Dec-20	100
IWA_WH_PRG	D7018	923	60158	FA101	B101305	HR		0	30-Dec-20	100
IWA_WH_PRG	D7018	923	60158	FA101	B101306	HR		738.35	30-Dec-20	100
IWA_WH_PRG	D7018	923	60158	FA101	B101307	HR		150	30-Dec-20	0
IWO_WH_PRG	D7018	923	60158	FA101	B100120	HR		1416.92	01-Nov-19	0
IWO_WH_PRG	D7018	923	60158	FA101	B100135	HR		1976.29	01-Nov-19	0
IWO_WH_PRG	D7018	923	60158	FA101	B100730	HR		316.06	01-Nov-19	0
IWO_WH_PRG	D7018	923	60158	FA101	B100830	HR		15.01	01-Nov-19	0
IWO_WH_PRG	D7018	923	60158	FA101	B100930	HR		99.58	01-Nov-19	0
IWO_WH_PRG	D7018	923	60158	FA101	B101005	HR		9.04	01-Nov-19	0
IWO_WH_PRG	D7018	923	60158	FA101	B101006	HR		1414.69	01-Nov-19	0
IWO_WH_PRG	D7018	923	60158	FA101	B101007	HR		322.34	01-Nov-19	0
IWO_WH_PRG	D7018	923	60158	FA101	B101305	HR		0	01-Nov-19	0
IWO_WH_PRG	D7018	923	60158	FA101	B101306	HR		738.35	01-Nov-19	0
IWO_WH_PRG	D7018	923	60158	FA101	B101307	HR		0	01-Nov-19	0

Result should be like below:

Drop Down Filter on Report
Effective Date	IAFC_ERN_PRDC	IWA_WH_PRG	IWO_WH_PRG
30-Dec-20	2675.38	2625.38	6308.28

[v-janeyg-msft · Accepted solution]

Hi, @MosesDhas 

It’s my pleasure to answer for you.

According to your description, If you don’t want the effective date to filter a certain type, it’s not very recommended that displaying in one visual, there will be ambiguity.

You can try like this:

IWO_WH_PRG  =SUMX(FILTER(ALL(Table1),[Transaction Type]="IWO_WH_PRG"),[Man Hours])

If it doesn’t solve your problem, please feel free to ask me.

Best Regards

Janey Guo

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