Forum Discussion
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/10/2018) - $$
Line 2 - HeaderID1 - DATE 3 (11/10/2018) - $$
Line 3 - HeaderID1 - DATE 4 (11/10/2018) - $$
Line 4 - HeaderID2 - DATE 1 (10/10/2018) - $$
Line 5 - HeaderID2 - DATE 3 (11/10/2018) - $$
Line 6 - HeaderID2 - DATE 4 (11/10/2018) - $$
I need create 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().
Thanks
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
5 Replies
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
Hi Juanli, YES, your test it's correct to figure my question.
Thanks a lot
Hi, the solution it's so easy :mansad:, I'm using Date Table (created based on other table hadn't contain future days)
Sorry
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?
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
