如果有基于剪切日期的数据，则操作最后两行

最后，我们定义了累积收入的衡量标准.它不需要任何特殊的逻辑，因为之前的措施已经妥善处理了它:

结果:

This question is a slightly varied version of this one...

Now I'm using Measures instead of Calculated columns and the date is static instead of having it based on a dropdown list.

Here's the Power BI test .pbix file:

drive.google/open?id=1OG7keqhdvDUDYkFQFMHyxcpi9Zi6Pn3d

This printscreen describes what I'm trying to accomplish:

Basically the date in P6 Update table is used as a cut date and will be fixedstatic. It's imported from an Excel sheet where the user can customize it however they want.

Here's what should happen when a matching row in Test data table is found for P6 Update date:

• column Earned Daily - must have its value summed with the next row if there's one;

• column Earned Cum - must grab the next row's value;

• all the previous rows should remain intact, that is, their values won't change;

• all subsequent rows must have their values assigned 0.

So for example:

If P6 Update is 1-May-2018, this is the expected result:

If P6 Update is 30-Apr-2018, this is the expected result:

If P6 Update is 29-Apr-2018, this is the expected result:

and so on...

Hope this makes sense.

This is easier in Excel, but trying to do this in Power BI is making me go nuts.

I will ignore previously asked related questions and start from scratch.

First, create a measure:

This measure will be used in other measures, to save you from typing all these conditions ("Actual Units" and "Current") again and again. It's a great practice to re-use measures in other measures - saves work, makes code cleaner and easier to refactor.

Create another measure:

We will use this measure whenever we need a cut off date. Please note that it does not have to be hard-coded - if P6 table contains a list of dates, you can create a pull-down slicer from the dates, and can choose the cut-off date dynamically. The formula will work properly.

Create third measure:

I am not sure if "Next Earn" is a good name for it, hopefully you will find a more intuitive name. The way it works: we save all necessary inputs into variables, and then use SWITCH function to define the results. Hopefully it's self-explanatory. (Note: if you need 0 above Cut Date, replace BLANK() with 0).

Finally, we define a measure for cumulative earn. It does not require any special logic, because previous measure takes care of it properly:

Result: