Remove duplicates within the period

Question & Answer
,
1

assuming I have following dataset

resourceid, details, timestamp, cost
111111,aaaaaaa,01-01-2023,100
111111,aaaaaaa,01-02-2023,100
111111,aaaaaaa,01-03-2023,100
111111,aaaaaaa,02-01-2023,100
111111,aaaaaaa,03-01-2023,100
111111,aaaaaaa,03-02-2023,100

how can I build a visual (or calc fields) which will show distinct monthly cost per resource. E.g. in this case 100 for Jan, Feb and Mar regardless of how many line items reported within that month. In other words how can I remove duplicates within the month and use only single record for monthly cost calculation?

2

Hi @Yuriy

I built a calculated field distinctCosts as follows

avgOver(avg(cost),[resourceid,truncDate("MM",timestamp),cost])

Then I created an analysis as follows:

image
image844×606 26.2 KB

This groups on cost and the month (the truncated date).

Does this help provide a solution?

Many Thanks,
Andrew