Calculate a change type based on the detail

[MiaZhao]

Hi Everyone,

I am new to pbi. I tried to classify the reason for the change number of our product.  

Customer	Category	Date	Amount	Cumulative total	Change Type
A	01	1.1	10	10	New
A	02	1.1	2	2	New
A	01	1.5	-2	8	reduction
A	01	1.6	4	14	addtion
A	01	2.1	-9	5	conversion
A	02	2.1	9	11	conversion

 My problem is how to calculate the change type column? There are more than 2 product categories, only 01 and 02 can be converted. The following formula was not correct.

IF('DetailData'[Amount]<0&&'DetailData'[Category]="01"&&CALCULATE(sum('DetailData'[Amount]),FILTER(ALLEXCEPT('DetailData','DetailData'[Category],'DetailData'[Customer]),'DetailData'[Category]="02"&& 'DetailData'[Date]<= LOOKUPVALUE('Canlendar Date'[Date],'Canlendar Date'[Date],'DetailData'[Date])))>=0,"Conversion","Others"

[v-jiascu-msft]

Hi @MiaZhao,

As you said, the formula is wrong. So by what conditions that we can say it's "conversion"?

Best Regards,

Dale

[MiaZhao · Accepted solution]

Hi @v-jiascu-msft

Thank you for your reply. I think I have sorted it out. I draw a logical map to tell what is the condition for a conversion, and it was quite complicated. I used EARLIER to calculate the total until the current date as well. The problem was solved. 

Thanks again. Have a nice weekend. 🙂

Regards,

Mia