Forum Discussion

pelucapampa's avatar
pelucapampa
Helper I
7 years ago
Solved

Filter with calculated measures

Hi, I've to Table Table1 Header Information: Have ID1 and Date (08/10/2018) Header Information: Have ID2 and Date (09/10/2018)     Table2 Detail information Line 1  - HeaderID1 - DATE 1 (10/1...
Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    7 years ago

    Hi pelucapampa

    Here are two ways you could try with creating calculated columns

    Way1:

    fill blank1 =
IF (
    [exchrate] = BLANK (),
    CALCULATE (
        SUM ( Sheet10[exchrate] ),
        FILTER ( ALL ( Sheet10 ), [date] = EARLIER ( Sheet10[date] ) - 1 )
    ),
    [exchrate]
)


fill blank2 =
IF (
    [fill blank1] = BLANK (),
    CALCULATE (
        SUM ( Sheet10[fill blank1] ),
        FILTER ( ALL ( Sheet10 ), [date] = EARLIER ( Sheet10[date] ) - 1 )
    ),
    [fill blank1]
)

    Way2: 

    weeknum = WEEKNUM([date],2)

fill blank3 =
IF (
    [exchrate] = BLANK (),
    VAR lastnoblankdate =
        CALCULATE (
            MAX ( [date] ),
            FILTER ( ALLEXCEPT ( Sheet10, Sheet10[weeknum] ), [exchrate] <> BLANK () )
        )
    RETURN
        CALCULATE (
            SUM ( Sheet10[exchrate] ),
            FILTER ( ALL ( Sheet10 ), [date] = lastnoblankdate )
        ),
    [exchrate]
)

     

    If you have holidays besides weekends,

    with way1, you need to create more columns to fill down all blank rows,

    with way2, you need to re-define the "weeknum" column to make it suitable for your scenario.

     

    Best Regards

    Maggie

pelucapampa's avatar
pelucapampa
Helper I
7 years ago

Hi, I've a table with Exchange Rate from web, obviously I haven't value for weekend and holidays date.

For that I've create a new table with all days, and then i create a column with Lookupvalue from WEBExchangeRate table.

Have you any ideas to complete blank days with the last day with exchRate?

 

v-juanli-msft's avatar
v-juanli-msft
Community Support
7 years ago

Hi pelucapampa

Here are two ways you could try with creating calculated columns

Way1:

fill blank1 =
IF (
    [exchrate] = BLANK (),
    CALCULATE (
        SUM ( Sheet10[exchrate] ),
        FILTER ( ALL ( Sheet10 ), [date] = EARLIER ( Sheet10[date] ) - 1 )
    ),
    [exchrate]
)


fill blank2 =
IF (
    [fill blank1] = BLANK (),
    CALCULATE (
        SUM ( Sheet10[fill blank1] ),
        FILTER ( ALL ( Sheet10 ), [date] = EARLIER ( Sheet10[date] ) - 1 )
    ),
    [fill blank1]
)

Way2: 

weeknum = WEEKNUM([date],2)

fill blank3 =
IF (
    [exchrate] = BLANK (),
    VAR lastnoblankdate =
        CALCULATE (
            MAX ( [date] ),
            FILTER ( ALLEXCEPT ( Sheet10, Sheet10[weeknum] ), [exchrate] <> BLANK () )
        )
    RETURN
        CALCULATE (
            SUM ( Sheet10[exchrate] ),
            FILTER ( ALL ( Sheet10 ), [date] = lastnoblankdate )
        ),
    [exchrate]
)

 

If you have holidays besides weekends,

with way1, you need to create more columns to fill down all blank rows,

with way2, you need to re-define the "weeknum" column to make it suitable for your scenario.

 

Best Regards

Maggie