DAX help needed to resolve slice and dice 8 weeks sum issue

[JacobLI]

Dear community friends, I'm slowing catching up with basic DAX stuff, and not surprisingly I came across a doubt again. 

Thanks @MFelix for resolving question in my last post, and this one is closely related to that.

link to last post: https://community.powerbi.com/t5/Desktop/Dax-help-needed-for-latest-8-weeks-total/m-p/190323#M83799

This is my data set, 

 Table 1
-------------------------------------------------------------------------------------
Stream | Product | Week Ending Date | 8 weeks ago Date | Flag Count
-------------------------------------------------------------------------------------
 Red  | A | 10/03/2017 | 13/01/2017 | 0
 Red  | A | 17/03/2017 | 20/01/2017 | 1 
 Red  | A | 24/03/2017 | 27/01/2017 | 1
 Red  | A | 31/03/2017 | 3/02/2017  | 0
 Red  | A | 7/04/2017  | 10/02/2017 | 1
 Red  | A | 14/04/2017 | 17/02/2017 | 0
 Red  | A | 21/04/2017 | 24/02/2017 | 0
 Red  | A | 28/04/2017 | 3/03/2017  | 0
 Red  | A | 5/05/2017  | 10/03/2017 | 1
 Red  | B | 10/03/2017 | 13/01/2017 | 0
 Red  | B | 17/03/2017 | 20/01/2017 | 1 
 Red  | B | 24/03/2017 | 27/01/2017 | 0
 Red  | B | 31/03/2017 | 3/02/2017  | 0
 Red  | B | 7/04/2017  | 10/02/2017 | 1
 Red  | B | 14/04/2017 | 17/02/2017 | 1
 Red  | B | 21/04/2017 | 24/02/2017 | 1
 Red  | B | 28/04/2017 | 3/03/2017  | 0
 Red  | B | 5/05/2017  | 10/03/2017 | 1
 Blue | E | 10/03/2017 | 13/01/2017 | 0
 Blue | E | 17/03/2017 | 20/01/2017 | 1 
 Blue | E | 24/03/2017 | 27/01/2017 | 1
 Blue | E | 31/03/2017 | 3/02/2017  | 0
 Blue | E | 7/04/2017  | 10/02/2017 | 1
 Blue | E | 14/04/2017 | 17/02/2017 | 0
 Blue | E | 21/04/2017 | 24/02/2017 | 0
 Blue | E | 28/04/2017 | 3/03/2017  | 0
 Blue | E | 5/05/2017  | 10/03/2017 | 1

I have created a calculated field called 8WeeksSum

8WeeksSum = 

CALCULATE(SUM([count]),
                       FILTER(ALLSELECTED(Table1)
                                  ,Table1[Week ending date] <= MAX(Table1[Week ending date]) && Table1[Week ending date] > MAX(Table1[8 weeks ago date]) 
                                  )
                     )

And when I tried to view data in a pivot table, below is the result that I see. Basically I cannot slice data on stream and product level, only date level. I reckon that ALLSELECTED function might play a role here but I'm really keen to understand the reason and how to solve this problem, any advice is welcomed, thanks a million!

 

[MFelix · Accepted solution]

HI @JacobLI,

My previous answer was based on the available information you gave didn't know you wanted to slice by additional levels of information, try this formula:

8WeeksSum = 
CALCULATE (
    SUM ( [ Flag Count] ),
    FILTER (
        ALLSELECTED ( Table2 ),
        Table2[ Week Ending Date ] <= MAX ( Table2[ Week Ending Date ] )
            && Table2[ Week Ending Date ] > MAX ( Table2[ 8 weeks ago Date ] )
    ),
    FILTER (
        ALLSELECTED ( Table2[Stream ] ),
        Table2[Stream ] = MAX ( Table2[Stream ] )
    ),
    FILTER (
        ALLSELECTED ( Table2[ Product ] ),
        Table2[ Product ] = MAX ( Table2[ Product ] )
    )
)

The end result is this:

Once again if you want that your final result compreend the entire table using slicers you should replace the ALLSELECTED  by ALL

Regards,

MFelix

[JacobLI]

Dear @MFelix, I was not in office until today, and I couldn't test out your solution. It works and thank you once again for helping me out!