Last 6 Weeks

[BI_Samurai]

Hello,

Could you help with me with a dax measure to calculate and display dynmically only last 6 weeks from the time period year-week selected in the slicer? Values should correspond with a single week result (no cumulative values are needed). Thank you.

[Anonymous · Accepted solution]

Hi @BI_Samurai ,

I create two tables as you mentioned.

Caleadar =
VAR StartYear =
    YEAR ( NOW () ) - 5
VAR EndYear =
    YEAR ( NOW () ) + 5
RETURN
    ADDCOLUMNS (
        CALENDAR ( DATE ( StartYear, 1, 1 ), DATE ( EndYear, 12, 31 ) ),
        "Year", YEAR ( [Date] ),
        "Date_Key", VALUE ( FORMAT ( [Date], "YYYYMMDD" ) ),
        "Month", MONTH ( [Date] ),
        "Month Name", FORMAT ( [Date], "MMMM" ),
        "Day", DAY ( [Date] ),
        "Year Month", VALUE ( FORMAT ( [Date], "YYYYMM" ) ),
        "Week", WEEKDAY ( [Date], 2 ),
        "Month Abbr", FORMAT ( [Date], "MMM" ),
        "Weekday Abbr", FORMAT ( [Date], "DDD" ),
        "Quarter", QUARTER ( [Date] ),
        "Quarter Name", "Q" & CONVERT ( QUARTER ( [Date] ), STRING )
    )

Year List =
SELECTCOLUMNS ( GENERATESERIES ( 2018, 2028, 1 ), "Year", [Value] )

Next I think you can create a measure and add it to Filter.

Is Show =
VAR SelectedYear =
    SELECTEDVALUE ( 'Year List'[Year] )
VAR CurrentYear =
    SELECTEDVALUE ( 'Caleadar'[Year] )
RETURN
    IF ( CurrentYear <= SelectedYear, "Y", "N" )

Finally you will get what you want.

Best Regards

Yilong Zhou

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

[Kedar_Pande]

Make sure you have a date table that includes columns for the year and week. Let’s assume your date table is named DateTable and it contains a column named jahr_woche.

DAX measure:

Last6WeeksValue = 
VAR SelectedWeek = SELECTEDVALUE(DateTable[jahr_woche]) // Get the selected year-week from slicer
VAR SelectedDate = 
    MAXX(
        FILTER(DateTable, DateTable[jahr_woche] = SelectedWeek), 
        DateTable[Date]
    ) // Get the date corresponding to the selected week
VAR StartDate = 
    CALCULATE(
        MAX(DateTable[Date]),
        FILTER(
            DateTable, 
            DateTable[Date] <= SelectedDate &&
            DateTable[Date] > EDATE(SelectedDate, -6) // Get last 6 weeks
        )
    )
RETURN
    CALCULATE(
        SUM('YourFactTable'[YourValueColumn]), // Replace with your actual fact table and column
        FILTER(
            DateTable, 
            DateTable[Date] > EDATE(StartDate, -6) &&
            DateTable[Date] <= StartDate
        )
    )

If this helped, a Kudos 👍 or Solution mark would be great!🎉
Cheers,
Kedar Pande
Connect on LinkedIn