cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Anonymous
Not applicable

Dax column to show last 4 weeks

I have a table that looks as follows:

 

Load                         Count

8/11/2019                   4673

8/18/2019                  3442

8/25/2019                  2334

9/1/2019                    4224

9/8/2019                    9472

 

(Load - All sundays/ weekly data)

At any point, I would like the report to show only the latest 4 weeks of data (in this case 8/18, 8/25, 9/1, 9/8) .. and another example is, next week (8/25, 9/1, 9/8, 9/15) 

 

Maybe a Dax that gives me "Last 4 weeks - Yes/NO" so that i can set the page level filter to Yes. Can this be done? Thank you!

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted

@Anonymous please try this

Rank = RANKX('Table','Table'[Load],,DESC,DENSE)

New Animated Dashboard: Sales Calendar


View solution in original post

Highlighted
Community Support
Community Support

Hi Akshaya, 

You could create a slicer table like below

341.PNG

Then create a measure like below

Measure 6 =
SWITCH (
    TRUE (),
    SELECTEDVALUE ( 'slicer'[slicer] ) = "next week", CALCULATE (
        SUM ( 'month'[count] ),
        FILTER (
            'month',
            WEEKNUM ( 'month'[Load] )
                >= WEEKNUM ( TODAY () ) - 3
                && WEEKNUM ( 'month'[Load] )
                    <= WEEKNUM ( TODAY () ) + 1
        )
    ),
    SELECTEDVALUE ( 'slicer'[slicer] ) = "last4week", CALCULATE (
        SUM ( 'month'[count] ),
        FILTER (
            'month',
            WEEKNUM ( 'month'[Load] )
                >= WEEKNUM ( TODAY () ) - 4
                && WEEKNUM ( 'month'[Load] ) <= WEEKNUM ( TODAY () )
        )
    )
)

342.PNG

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
Highlighted
Super User I
Super User I

@Anonymous  one way of doing that would be through calculated columns as following

Rank = RANKX('Table','Table'[Load],,DESC)

and always put filter from 1-4

 

1e.PNG


New Animated Dashboard: Sales Calendar


Highlighted
Anonymous
Not applicable

Week Ranking.png 

@smpa01  This is the output i am getting. Week Rank is a calculated column (Dont Summarize enabled)

Highlighted
Helper V
Helper V

Try this:

=    IF(
        [Load] >= TODAY() - 30,
        "Y",
        BLANK()
    )
And then you can filter Y!
🙂
Highlighted

@Anonymous please try this

Rank = RANKX('Table','Table'[Load],,DESC,DENSE)

New Animated Dashboard: Sales Calendar


View solution in original post

Highlighted
Community Support
Community Support

Hi Akshaya, 

You could create a slicer table like below

341.PNG

Then create a measure like below

Measure 6 =
SWITCH (
    TRUE (),
    SELECTEDVALUE ( 'slicer'[slicer] ) = "next week", CALCULATE (
        SUM ( 'month'[count] ),
        FILTER (
            'month',
            WEEKNUM ( 'month'[Load] )
                >= WEEKNUM ( TODAY () ) - 3
                && WEEKNUM ( 'month'[Load] )
                    <= WEEKNUM ( TODAY () ) + 1
        )
    ),
    SELECTEDVALUE ( 'slicer'[slicer] ) = "last4week", CALCULATE (
        SUM ( 'month'[count] ),
        FILTER (
            'month',
            WEEKNUM ( 'month'[Load] )
                >= WEEKNUM ( TODAY () ) - 4
                && WEEKNUM ( 'month'[Load] ) <= WEEKNUM ( TODAY () )
        )
    )
)

342.PNG

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors