Filter function in Calculate not giving the expected result

[Kartic21]

Hi Experts , 

Filter function in Calculate not giving the expected result. Trying to get the sum of max timekey in each quarter.

Show of Max Sales = CALCULATE(sum(Sales[Sales]),FILTER('Calendar data','Calendar data'[TimeKey]=[Max TimeKey_Sales]))

Max TimeKey_Sales is giving right number but same is not passing in the filter function when used with calculate

file uploaded here :

https://drive.google.com/drive/folders/1OWC8LjuLAU_3BaMXOa53vjlEX29ybfDU

thanks in advance for the help

[Zubair_Muhammad]

@Kartic21 

Try putting the MEASURE in a variable first

i.e.

Show of Max Sales =
VAR mymeasure = [Max TimeKey_Sales]
RETURN
    CALCULATE (
        SUM ( Sales[Sales] ),
        FILTER ( 'Calendar data', 'Calendar data'[TimeKey] = mymeasure )
    )

[v-juanli-msft]

Hi @Kartic21 

I have no access to your file.

I make a test on my side:

1.

Create a calendar table

calendar = ADDCOLUMNS( CALENDARAUTO(),"year",YEAR([Date]),"month",MONTH([Date]))

Add columns in this table

quarter = SWITCH(TRUE(),[month] in {1,2,3},1,[month] in {4,5,6},2,[month] in {7,8,9},3,[month] in {10,11,12},4)

2.

Create columns in your original table

year-quarter =
YEAR ( [date] ) & "-"
    & SWITCH (
        TRUE (),
        MONTH ( Sheet6[date] ) IN { 1, 2, 3 }, 1,
        MONTH ( Sheet6[date] ) IN { 4, 5, 6 }, 2,
        MONTH ( Sheet6[date] ) IN { 7, 8, 9 }, 3,
        MONTH ( Sheet6[date] ) IN { 10, 11, 12 }, 4
    )

Create measures

max date for each quarter = CALCULATE(MAX(Sheet6[date]),FILTER(ALL(Sheet6),Sheet6[year-quarter]=MAX(Sheet6[year-quarter])))

sum = CALCULATE(SUM(Sheet6[sales]),FILTER(Sheet6,Sheet6[year-quarter]=MAX(Sheet6[year-quarter])&&Sheet6[date]=[max date for each quarter]))

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Kartic21]

https://drive.google.com/file/d/1zBng1DSDTzhXFhYhnsp8QcEMLu5J9Q_1/view?usp=sharing

this link should be accessible

[v-juanli-msft · Accepted solution]

Hi @Kartic21 

Create two measures in "Sales" table

maxtime = CALCULATE(MAX(Sales[TimeKey]),ALLSELECTED('Calendar data'))

Measure = CALCULATE(SUM(Sales[Sales]),FILTER(ALLSELECTED('Calendar data'),'Calendar data'[TimeKey]=[maxtime]))

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.