Entering Data using date column as a filter

[Anonymous]

Hello,

I have the following dataset, where PK_Date represents a date column of every date from 2015-2020. In the column SHIP_UNITS_BY_UOM, values will either be non-zero or blank(). I would like to create a measure to add 0 to the SHIP_UNITS_BY_UOM column up to the current date for every value in PK_DATE and leave the remaining entries blank.

In the BW_ShipZeroTest2 measure, I have tried adding 0 based on a filter using BW_ShipZeroTest2 = calculate(sum('F_INVOICE'[SHIP_UNITS_BY_UOM),filter(D_DATE,'D_DATE'[PK_Date]<=today())) + 0, but this adds 0 to all fields. Is this possible to do in a measure, where the 0 does not extend past today's date?

I want to try something like Measure = if(D_Date[PK_Date]<today() && calculate(sum('F_INVOICE'[SHIP_UNITS_BY_UOM]) = blank(), sum('F_INVOICE'[SHIP_UNITS_BY_UOM]) + 0, blank())); however, I cannot use D_DATE[PK_Date] in the if statement without selecting a single value, and I'm unsure which function to use. Would this logic work?

Thank you for your time!

[Anonymous]

I tried your formula and it was not able to work; on rows with values in SHIP_UNITS_BY_UOM because the conditional was being flagged false. Using your approach, however, I used a nested if statement and it worked perfectly! Final formula is here:

BW_ShipZeroTest2 = if( max(D_DATE[PK_Date])<= today(),if(sum(F_INVOICE[SHIP_UNITS_BY_UOM])=blank(),0,sum(F_INVOICE[SHIP_UNITS_BY_UOM])),blank())

Thank you for the help in using the MAX() function.

[v-shex-msft · Accepted solution]

HI @Anonymous,

You can try to use below formula if it works for your scenario:

Measure =
IF (
    MAX ( D_Date[PK_Date] ) < TODAY ()
        && SUM ( 'F_INVOICE'[SHIP_UNITS_BY_UOM] ) = BLANK (),
    SUM ( 'F_INVOICE'[SHIP_UNITS_BY_UOM] ) + 0,
    BLANK ()
)

If my formula not help, please share some sample data to test and coding formula.

Regards,

Xiaoxin Sheng

[Anonymous]

Thank you for the quick response. I just tried it on the dataset, and it works where all fields past today() are blank; however, there is something flagging the if statement FALSE when summing SHIP_UNITS_BY_UOM as now all of those entries are blank() as well. See image below and linked dataset ( https://transfernow.net/755714110tj2) with result.