Question on Calculations

Question

I have the below data. &nbsp;What I need to do is track total sales and total transactions, and when either of these go over their threshold then the date gets placed in the Date Threshold Met, but it is the date of the first of the month. &nbsp;Then that date never changes. &nbsp;Is there a way to populate the Date Threshold Met and then not have it change, as once it is met it is done. &nbsp;Basically what I have is several rows by product.&nbsp;Thanks in advance!&nbsp;Total SalesTotal TransactionsSales ThreshodTransaction ThresholdDate Threshold Met&nbsp;95000178100000200&nbsp;&nbsp;&nbsp;

jthomson · Answer

Without seeing your data more clearly, I'd guess the way to do it would be to make some sort of cumulative sum measure on sales for the month, then work out the lowest value for where that measure is higher than your threshhold value?

parry2k · Answer

Razorbx13&nbsp;Read this post to get your answer quickly. https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

razorbx13 · Answer

Basically the sales and counts will increase each night the data is refreshed. The thresholds are static.&nbsp; I just want first of the month to go into the date field when the threshold is met.&nbsp; So if it is met on August 21, 2020 the date would be August 1, 2020 and then would never change.

v-lili6-msft · Answer

hi&nbsp;&nbsp;Razorbx13&nbsp;
What is the meet date? do you mean it is the date of refresh date? If so, it is not achieve in power bi, since whether it's a calculate or custom column or a measure, they all depends on their row context, it means, after you refresh data, it will&nbsp;recalculate again based on their row context.
&nbsp;
Regards,
Lin
&nbsp;
&nbsp;

razorbx13 · Answer

So to clarify, what I am doing is analyzing total sales and total transaction counts.&nbsp; The table has a field for total sales and a field for total transaction count by the State the sale was made.&nbsp; It also has a stagnant field for the total sales threshold and total sales count of transaction by State.&nbsp; What I am wanting to do is have a calculated field or something that shows when either of the thresholds are passed.&nbsp; This field would be called Date Threshold Met.&nbsp;Example:&nbsp; For Alaska the threshold is 100,000 in total sales and 200 in total count of transactions.&nbsp; Actual sales for August is 95,000 and total transaction count is 175.&nbsp; In September, the sales goes to 103,458 which passes the threshold of 100,000.&nbsp; At that point the field called Date Threshold met would be 09/01/2020.&nbsp; The first of the month the threshold was met or passed.&nbsp;&nbsp;Second, this date would then never change as once a threshold is passed, it is passed.&nbsp; If it has not passed either of the thresholds, the field is NULL.&nbsp;

Forum Discussion

Question on Calculations

6 Replies

Related content

calculation question

Bookmark questions

Architecture question and brainstorming

Unpivot Question

Field Parameter Question

Total Sales	Total Transactions	Sales Threshod	Transaction Threshold	Date Threshold Met
95000	178	100000	200