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 have a chart that has the x-axis the weeks and as a value the safety stock. I need you to see in the graph the difference between one week and another.
For example. Week 30 there are 399 and week 31 there are 398. So, I need to see in week 31 the value of the difference, that is: -1. So on.
Thanks a lot!
Solved! Go to Solution.
At first, try my code to build a date table. If you use weeknum, you will get confused at the begining of next year.
For example, 2020/12/31 and 2021/01/01 are in the same week, but weeknum will show you 53 and 1. That's incorrect and will make our calculate difficult.
Date table:
Date =
ADDCOLUMNS (
CALENDARAUTO (),
"Year", YEAR ( [Date] ),
"Month", MONTH ( [Date] ),
"Weeknum", WEEKNUM ( [Date], 2 ),
"YearMonth",
YEAR ( [Date] ) * 100
+ MONTH ( [Date] )
)
Add calculated columns:
ISO 8601 WeekNum =
VAR _COUNT0 =
CALCULATE (
COUNTROWS ( 'Date' ),
FILTER (
'Date',
'Date'[Year] = EARLIER ( 'Date'[Year] )
&& 'Date'[WeekNum] - 1 = 0
)
)
VAR _BASENUM1 =
IF ( _COUNT0 < 7, 'Date'[WeekNum] - 1, 'Date'[WeekNum] )
VAR _ISO_8601_WeekNum =
IF (
WEEKDAY ( DATE ( 'Date'[Year] - 1, 01, 01 ) ) <> 1
&& 'Date'[Year] = 'Date'[Year]
&& _BASENUM1 = 0,
WEEKNUM ( DATE ( MIN ( 'Date'[Year] ), 12, 31 ), 1 ) - 1,
_BASENUM1
)
RETURN
_ISO_8601_WeekNum
ISO_Year =
VAR _COUNT0 =
CALCULATE (
COUNTROWS ( 'Date' ),
FILTER (
'Date',
'Date'[Year] = EARLIER ( 'Date'[Year] )
&& 'Date'[WeekNum] - 1 = 0
)
)
VAR _BASENUM1 =
IF ( _COUNT0 < 7, 'Date'[WeekNum] - 1, 'Date'[WeekNum] )
RETURN
IF(_BASENUM1 = 0,'Date'[Year] -1,'Date'[Year])
ISO YearWeekNum = 'Date'[ISO_Year]*100+'Date'[ISO 8601 WeekNum]
Build a relationship between your data table and this calendar date table by date column.
Create a measure as below.
Diff =
VAR _CurValue =
CALCULATE ( SUM ( 'Sample'[Value] ) )
VAR _LastISOYearWeekNum =
MAXX (
FILTER (
ALL ( 'Date' ),
'Date'[ISO YearWeekNum] < MAX ( 'Date'[ISO YearWeekNum] )
),
'Date'[ISO YearWeekNum]
)
VAR _LastValue =
CALCULATE (
SUM ( 'Sample'[Value] ),
FILTER ( ALL ( 'Date' ), 'Date'[ISO YearWeekNum] = _LastISOYearWeekNum )
)
RETURN
_CurValue - _LastValue
Result is as below.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
At first, try my code to build a date table. If you use weeknum, you will get confused at the begining of next year.
For example, 2020/12/31 and 2021/01/01 are in the same week, but weeknum will show you 53 and 1. That's incorrect and will make our calculate difficult.
Date table:
Date =
ADDCOLUMNS (
CALENDARAUTO (),
"Year", YEAR ( [Date] ),
"Month", MONTH ( [Date] ),
"Weeknum", WEEKNUM ( [Date], 2 ),
"YearMonth",
YEAR ( [Date] ) * 100
+ MONTH ( [Date] )
)
Add calculated columns:
ISO 8601 WeekNum =
VAR _COUNT0 =
CALCULATE (
COUNTROWS ( 'Date' ),
FILTER (
'Date',
'Date'[Year] = EARLIER ( 'Date'[Year] )
&& 'Date'[WeekNum] - 1 = 0
)
)
VAR _BASENUM1 =
IF ( _COUNT0 < 7, 'Date'[WeekNum] - 1, 'Date'[WeekNum] )
VAR _ISO_8601_WeekNum =
IF (
WEEKDAY ( DATE ( 'Date'[Year] - 1, 01, 01 ) ) <> 1
&& 'Date'[Year] = 'Date'[Year]
&& _BASENUM1 = 0,
WEEKNUM ( DATE ( MIN ( 'Date'[Year] ), 12, 31 ), 1 ) - 1,
_BASENUM1
)
RETURN
_ISO_8601_WeekNum
ISO_Year =
VAR _COUNT0 =
CALCULATE (
COUNTROWS ( 'Date' ),
FILTER (
'Date',
'Date'[Year] = EARLIER ( 'Date'[Year] )
&& 'Date'[WeekNum] - 1 = 0
)
)
VAR _BASENUM1 =
IF ( _COUNT0 < 7, 'Date'[WeekNum] - 1, 'Date'[WeekNum] )
RETURN
IF(_BASENUM1 = 0,'Date'[Year] -1,'Date'[Year])
ISO YearWeekNum = 'Date'[ISO_Year]*100+'Date'[ISO 8601 WeekNum]
Build a relationship between your data table and this calendar date table by date column.
Create a measure as below.
Diff =
VAR _CurValue =
CALCULATE ( SUM ( 'Sample'[Value] ) )
VAR _LastISOYearWeekNum =
MAXX (
FILTER (
ALL ( 'Date' ),
'Date'[ISO YearWeekNum] < MAX ( 'Date'[ISO YearWeekNum] )
),
'Date'[ISO YearWeekNum]
)
VAR _LastValue =
CALCULATE (
SUM ( 'Sample'[Value] ),
FILTER ( ALL ( 'Date' ), 'Date'[ISO YearWeekNum] = _LastISOYearWeekNum )
)
RETURN
_CurValue - _LastValue
Result is as below.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Syndicate_Admin , You need an independent week or year week table, with Rank on Year week.
New column in Date or Ywar week column
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
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))
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 |
---|---|
113 | |
99 | |
82 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |