Forum Discussion

pelucapampa's avatar
pelucapampa
Icon for Helper I rankHelper 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

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

Hi pelucapampa

Not very clear about your question,Please read my test and point out whether my understanding is right.

Are your tables like below, date format is DD/MM/YYYY

"a Measure that contain SUM from Table2 using Slicer Date Filter (Ex. This Month for DETAIL TABLE), but filtering the Header's ID when Table1 Date it's TODAY()."

 

This means:

when the Slicer Date Filter is set as october, then rows in Table2 which meet this condition is as below

Header DATE $$
ID1 10/10/2018 100
ID1 11/10/2018 200
ID1 11/10/2018 300
ID2 10/10/2018 400
ID2 11/10/2018 500
ID2 11/10/2018 600

 

iltering the Header's ID when Table1 Date it's TODAY().

then rows in Table1 which meet this condition is as below, assume today is 2018/10/9

ID Date
   
ID2 9/10/2018

then rows in Table2 which meet this condition is as below

ID2 10/10/2018 400
ID2 11/10/2018 500
ID2 11/10/2018 600

Then calculate the sum : 400+500+600

 

Best Regards

maggie

  • pelucapampa's avatar
    pelucapampa
    Icon for Helper I rankHelper I
    7 years ago

    Hi Juanli, YES, your test it's correct to figure my question.

    Thanks a lot

  • pelucapampa's avatar
    pelucapampa
    Icon for Helper I rankHelper I
    7 years ago

    Hi, the solution it's so easy :mansad:, I'm using Date Table (created based on other table hadn't contain future days)

    Sorry

  • pelucapampa's avatar
    pelucapampa
    Icon for Helper I rankHelper 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
      Icon for Community Support rankCommunity 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