Dax column to show last 4 weeks

[Anonymous]

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!

[smpa01 · Accepted solution]

@Anonymous please try this

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

[dax · Accepted solution]

Hi Akshaya, 

You could create a slicer table like below

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 () )
        )
    )
)

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.

[sy898661]

Try this:

=    IF(
        [Load] >= TODAY() - 30,
        "Y",
        BLANK()
    )

And then you can filter Y!

🙂

[smpa01]

@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

[Anonymous]

 

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

[smpa01 · Accepted solution]

@Anonymous please try this

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