Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Entering Data using date column as a filter

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 ...
Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    8 years ago

    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's avatar
Anonymous
Not applicable
8 years ago

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.