Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Last 6 Weeks

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 singl...
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi Anonymous ,

    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's avatar
Kedar_Pande
Icon for Super User rankSuper User
1 year ago

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