Question on Calculations

[Razorbx13]

I have the below data.  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.  Then that date never changes.  Is there a way to populate the Date Threshold Met and then not have it change, as once it is met it is done.  Basically what I have is several rows by product.

Thanks in advance!

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

[jthomson]

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?

[Razorbx13]

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

[v-lili6-msft]

hi  @Razorbx13 

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 recalculate again based on their row context.

Regards,

Lin

[Razorbx13]

So to clarify, what I am doing is analyzing total sales and total transaction counts.  The table has a field for total sales and a field for total transaction count by the State the sale was made.  It also has a stagnant field for the total sales threshold and total sales count of transaction by State.  What I am wanting to do is have a calculated field or something that shows when either of the thresholds are passed.  This field would be called Date Threshold Met.

Example:  For Alaska the threshold is 100,000 in total sales and 200 in total count of transactions.  Actual sales for August is 95,000 and total transaction count is 175.  In September, the sales goes to 103,458 which passes the threshold of 100,000.  At that point the field called Date Threshold met would be 09/01/2020.  The first of the month the threshold was met or passed. 

Second, this date would then never change as once a threshold is passed, it is passed.  If it has not passed either of the thresholds, the field is NULL. 

[v-lili6-msft]

hi @Razorbx13 

The problem is that how to define this date column, for custom column in power query and calculate data in DAX, it all will recalculate after data refresh, it won't never change as once a threshold is passed,

for your case, you'd better get it in datasource not in power bi.

Regards,

Lin

[parry2k]

@Razorbx13 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