Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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,
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
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
@Mercator_1980,
Since this thread has been closed, you can open new threads to describe your questions.
Regards,
Lydia
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |