Forum Discussion
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 single week result (no cumulative values are needed). Thank you.
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.
2 Replies
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 LinkedInHi 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.
