cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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
Highlighted
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


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
Highlighted
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


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 Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors