Forum Discussion
Question on Calculations
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?
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.
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
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.
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
