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.
I have a ta ble of transactions(events) for a list of items. Each transaction (event) is assgined a value . This value is accumulated for each item. the business logic requires that when the accumulated value of a particular item (let's say item 'A') exceeds a threshold, this triggers an action from the user.
Now I need to see the date at which an item had accumulated enough values to exceed the threshold (of let's say '2').
Note that there is a time at which a counter resets. and the accumulated values is only summing the values between the reset date and current event date. So I need to get the values of ??? as shown in the table below.
I thought to create a new column in the first table to list the accumulated values for each event date and then pull the date at which the accumulated value exceeded the threshold to the second table. But unfortunately, I couldn't add this column. Can you help me add a column that accumulated the values of the counter for each item between the event date and the counter reset date.
thanks.
Ali
Solved! Go to Solution.
Hi @Anonymous ,
We can create what if parameter firstly.
Atfer that, create a calcualted column to get the running total.
running = CALCULATE(SUM('Table'[Value]),FILTER('Table','Table'[Date]<=EARLIER('Table'[Date])))
Then we can get the date which we want.
Measure = CALCULATE(MAX('Table'[Date]),FILTER('Table','Table'[running] = Parameter[Parameter Value]))
Hi @Anonymous ,
Sorry I cannot get you, kindly share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.
thnaks for reaching out to help. I don't own the data and I cannot unfortunatley share it. Bad for me.
I'll try to rephrase my question, to make it less confusing.
basically, the problem boils down to adding a new column. This new column holds the summation of values from another column up to this row. so, if i have the following table, I need to calculate the new column to hold the running summation.
Value Date (new column to hold the running summation)
1 1 Jan 19 1
2 19 Feb 19 3
2 23 Apr 19 5
1 30 Jun 19 6
4 1 Aug 19 10
2 1 sep 19 12
Then I need to report the date at which the new column value is 10 (in this case, 1 August 19).
Thank you.
Ali
Hi @Anonymous ,
We can create what if parameter firstly.
Atfer that, create a calcualted column to get the running total.
running = CALCULATE(SUM('Table'[Value]),FILTER('Table','Table'[Date]<=EARLIER('Table'[Date])))
Then we can get the date which we want.
Measure = CALCULATE(MAX('Table'[Date]),FILTER('Table','Table'[running] = Parameter[Parameter Value]))
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 |
---|---|
110 | |
99 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |