Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello Everyone
hope you stay healthy
I have problem for calculate actual by Last Week, I don't wanna use weeknum for this case.
I use this week method (Month-Week) for calculated my actual, can I apply dax calculate to find the actual number from last week with my week method?
Month-Week =
VAR _DAY = DAY([Date]) RETURN
SWITCH( TRUE(),
_DAY >= 1 && _DAY <= 7, 'calendar'[bulan3] & " " & "Week 1",
_DAY >= 8 && _DAY <= 14, 'calendar'[bulan3] & " " & "Week 2",
_DAY >= 15 && _DAY <= 21, 'calendar'[bulan3] & " " & "Week 3",
'calendar'[bulan3] & " " & "Week 4"
)
please describe in detail
thank you guys
Solved! Go to Solution.
Hi @Anonymous ,
I created a sample pbix file(see attachment) for you, please check whether that is what you want. You can create a measure as below:
Previous week sales =
VAR _selyear =
SELECTEDVALUE ( 'calendar'[Year] )
VAR _selmonth =
SELECTEDVALUE ( 'calendar'[Month] )
VAR _selweeknum =
SELECTEDVALUE ( 'calendar'[Weeknum] )
RETURN
CALCULATE (
SUM ( 'Table'[Actual] ),
FILTER (
ALLSELECTED ( 'calendar' ),
'calendar'[Year] = _selyear
&& IF (
_selweeknum = 1,
'calendar'[Month] = _selmonth - 1
&& 'calendar'[Weeknum] = 4,
'calendar'[Month] = _selmonth
&& 'calendar'[Weeknum] = _selweeknum - 1
)
)
)
If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.
How to upload PBI in Community
Best Regards
Hi @Anonymous ,
I created a sample pbix file(see attachment) for you, please check whether that is what you want. You can create a measure as below:
Previous week sales =
VAR _selyear =
SELECTEDVALUE ( 'calendar'[Year] )
VAR _selmonth =
SELECTEDVALUE ( 'calendar'[Month] )
VAR _selweeknum =
SELECTEDVALUE ( 'calendar'[Weeknum] )
RETURN
CALCULATE (
SUM ( 'Table'[Actual] ),
FILTER (
ALLSELECTED ( 'calendar' ),
'calendar'[Year] = _selyear
&& IF (
_selweeknum = 1,
'calendar'[Month] = _selmonth - 1
&& 'calendar'[Weeknum] = 4,
'calendar'[Month] = _selmonth
&& 'calendar'[Weeknum] = _selweeknum - 1
)
)
)
If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.
How to upload PBI in Community
Best Regards
sorry to late response Ms Ying
thanks for your answer
can you help me I just wanna showing last week actual only.
if actual from 1 June until 19 June, I just wanna see cummulative only week 3 (15 June, 16 June, 17 June, 18 June & 19 June).
@Anonymous , I usually prefer to create week rank or Month Week, based on need and can work on this week vs last week
Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
OR
Week Rank = RANKX(all('Date'),'Date'[Month Week No],,ASC,Dense)
Then 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))
If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.