cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
JacobLI Frequent Visitor
Frequent Visitor

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

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!

 

 Capture.PNG

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

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

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:

 

8weekscalc_revised.png

 

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

 

Regards,

MFelix



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




View solution in original post

2 REPLIES 2
Super User
Super User

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

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:

 

8weekscalc_revised.png

 

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

 

Regards,

MFelix



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




View solution in original post

JacobLI Frequent Visitor
Frequent Visitor

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

 

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! 

Helpful resources

Announcements
New Ranks and Rank Icons in 2020

New Ranks and Rank Icons in 2020

Read the announcement for more information!

New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

November 2019 Community Highlights

November 2019 Community Highlights

Get an overview of the events and great community content from November.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)