Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I am trying to calculate a count of 4 seperate sets of weeks, one of which is static and the other is based on the selected week or month.
Ideally it would look like this, and from there I can run my other calculations. I have a table KeyMeasures that have Schedule 2 & 3 defining the count of weeks but currently i am only returning the weeks that are selected. not the 13 and 4.
Schedule | Weeks |
1 | Selected weeks last year |
2 | 13 (Static) |
3 | 4 (Static) |
4 | Selected weeks this year |
right now my formula looks like this,
PRJ_WKS = DISTINCT(KeyMeasures[WK])
SEL_WKS = DISTINCTCOUNT(Calendar_Table[WK_ST_DATE])
WKS2 =
VAR RptWeek =
MAX(Calendar_Table[WK_ST_DATE])
VAR Calc1 =
CALCULATE(
[WKS],
Calendar_Table[CALENDAR_DAY] <= RptWeek)
VAR Project = CALCULATE([PRJ_WKS],
ALL(Calendar_Table))
VAR PRJ_2 = CALCULATE(SUM(KeyMeasures[WK]), KeyMeasures[2 SORT] = 2)
VAR PRJ_3 = CALCULATE(SUM(KeyMeasures[WK]), KeyMeasures[2 SORT] = 3)
RETURN
IF(PRJ_2 = 13,PRJ_2, IF(PRJ_3 = 4,PRJ_3,Calc1))
Solved! Go to Solution.
@calexander24 , You can use week rank to compare the data and for static you use week number
example
new columns needed as per need
new columns
Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)
Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
OR
Week Rank = RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYWW format
example measures
This Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))
Last year Week= CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=(max('Date'[Week Rank]) -52)))
This week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year]) && 'Date'[Week] = Max('Date'[Week]) ))
Last year same week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year])-1 && 'Date'[Week] = Max('Date'[Week])))
@calexander24 , You can use week rank to compare the data and for static you use week number
example
new columns needed as per need
new columns
Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)
Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
OR
Week Rank = RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYWW format
example measures
This Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))
Last year Week= CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=(max('Date'[Week Rank]) -52)))
This week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year]) && 'Date'[Week] = Max('Date'[Week]) ))
Last year same week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year])-1 && 'Date'[Week] = Max('Date'[Week])))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
94 | |
83 | |
66 | |
59 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |