Calculate Differences of cumulative values by factorial group and date OR id

[Mercator_1980]

Dear all,

kind of a beginners question i am sure but i can't wrap my head around it.

I have the following fake data, which illustrates my question:

As you can see we have a customer_number (which is just an id - not a sum). These customers are tracked by date and documentnumber (basically the documentnumber is just the count, how many datarows have been reached yet - the date defines the day of collecting the data). for each row the "Amount_cumulative" is being displayed - these are fake numbers which show the cumulative number of items that have been bought by said customers at date x / documentnumber x.

What is to be achieved:

Column "E" (CALCULATED_DIFF_PER_DATE) is the to be calculated column which shows the iterative difference of the cumulative Amount of column "B" (Amount_cumulative) defined by the categorical group "Customer_number" AND "Date". Example - for customer "100" we have 50 items IN TOTAL (cumul) bought on the 01.01.2018. For the identical customer, we have 70 items IN TOTAL (cumul) bougth on 03.01.2018. That calculates to a difference of 20 items bought between the two dates. Only the difference between neighbouring dates per customer is to be calculated.

How can this calculation of a new column which displays the iterative Difference by customer AND Date (or) Documentnumber be achieved in Power-BI?

What i tried so far:

Reverse_cumul = 
CALCULATE(
SUM(table[Amount_cumulative]);
ALLEXCEPT(table;table[Customer_number]);
table[Date] <= EARLIER(table[Date])
) 

This will not work as SUM(table[Amount_cumulative]) itself creates a running Total - but i just don`t know how to tell PBi that i want the difference per date by Customer.

Thank you for your help!

Marc

[Anonymous · Accepted solution]

@Mercator_1980,

Please use DAX below.

CALCULATED_DIFF_PER_DATE = var previous = CALCULATE(FIRSTNONBLANK(Table[Amount_cumulative];Table[Amount_cumulative]);FILTER(Table;Table[Customer_number]=EARLIER(Table[Customer_number]) && Table[Dokument-ID]=EARLIER(Table[Dokument-ID])-1)) return IF(ISBLANK(previous);0; Table[Amount_cumulative]-previous)

Regards,
Lydia

[Mercator_1980]

Dear Lydia,

your Solution works perfectly for me! Thank you so very much.

I find the DAX confusing, but this is because i have barely any knowledge in it and will work through it - may i contact you via posting my Questions in this forum if i have any further Questions?

With best regards

Marc

[Anonymous]

@Mercator_1980,

Since this thread has been closed, you can open new threads to describe your questions.

Regards,
Lydia