Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
"Income PY" using SAMEPERIODLASTYEAR shows incorrect values if showing data by week, because every year Week 1 starts on Different dates.
I could fix this using the Measure "Income PY same Week" but it not shows me a Total. Anyone can help me in fixing this?
I attach herbey the PowerBI-File DemoAdventureWorks for Easy Reference. Any Help arrpeciated!
Solved! Go to Solution.
See if these can help for last year
Last year Week= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=(max('Date'[Week Rank]) -52)))
YTD= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year]) && 'Date'[Week] <= Max('Date'[Week]) ))
LYTD = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year])-1 && 'Date'[Week] <= Max('Date'[Week])))
You can also try
1 year back =
var _week = maxx(allselected(Date), Date[WEEK])
var _year = maxx(allselected(Date), Date[Year])
return
CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year]=_year-1 && 'Date'[Week] = _week))
This was my final code. Thanks again for your precious help!
VAR IncomeSameWeekLastYear =
CALCULATE (
[Income],
FILTER (
ALL ( DIMDate ),
DIMDate[Year]
= SELECTEDVALUE ( DIMDate[Year] ) - 1
&& DIMDate[WeekNo] = SELECTEDVALUE ( DIMDate[WeekNo] )
)
)
VAR _week =
MAXX ( ALLSELECTED ( DIMDate), DIMDate[WeekNo] )
VAR _year =
CALCULATE(MAXX ( ALLSELECTED ( DIMDate), DIMDate[Year] ))
VAR IncomeSameWeekLastYearTotal =
CALCULATE (
[Income],
FILTER (
ALL ( DIMDate ),
DIMDate[Year] = _year - 1
&& DIMDate[WeekNo] <= _week
)
)
RETURN
SWITCH(
ISFILTERED ( DIMDate[WeekNo] ), //looks if a Weekno is displayed
TRUE (), IncomeSameWeekLastYear,
SWITCH(ISFILTERED(DIMDate[MonthName]), //looks if a Monthname is displayed
TRUE(), [Income PY],
IncomeSameWeekLastYearTotal
)
)
@miraglia , refer my WOW blog that can help
Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...
https://www.youtube.com/watch?v=pnAesWxYgJ8
Usually same week last year is 364 days behind, refer example
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-364,Day))
Thanks for your Blog Link. However the Problem is not to have the Sales by Week of Last Year. The Total of the Weeks of Last Year is missing also in your DAX Formuals of your Blog. There where on the Printscreen shows DaxCode? should be the Total of the showed values
See if these can help for last year
Last year Week= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=(max('Date'[Week Rank]) -52)))
YTD= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year]) && 'Date'[Week] <= Max('Date'[Week]) ))
LYTD = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year])-1 && 'Date'[Week] <= Max('Date'[Week])))
You can also try
1 year back =
var _week = maxx(allselected(Date), Date[WEEK])
var _year = maxx(allselected(Date), Date[Year])
return
CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year]=_year-1 && 'Date'[Week] = _week))
This was my final code. Thanks again for your precious help!
VAR IncomeSameWeekLastYear =
CALCULATE (
[Income],
FILTER (
ALL ( DIMDate ),
DIMDate[Year]
= SELECTEDVALUE ( DIMDate[Year] ) - 1
&& DIMDate[WeekNo] = SELECTEDVALUE ( DIMDate[WeekNo] )
)
)
VAR _week =
MAXX ( ALLSELECTED ( DIMDate), DIMDate[WeekNo] )
VAR _year =
CALCULATE(MAXX ( ALLSELECTED ( DIMDate), DIMDate[Year] ))
VAR IncomeSameWeekLastYearTotal =
CALCULATE (
[Income],
FILTER (
ALL ( DIMDate ),
DIMDate[Year] = _year - 1
&& DIMDate[WeekNo] <= _week
)
)
RETURN
SWITCH(
ISFILTERED ( DIMDate[WeekNo] ), //looks if a Weekno is displayed
TRUE (), IncomeSameWeekLastYear,
SWITCH(ISFILTERED(DIMDate[MonthName]), //looks if a Monthname is displayed
TRUE(), [Income PY],
IncomeSameWeekLastYearTotal
)
)
User | Count |
---|---|
47 | |
26 | |
21 | |
17 | |
15 |
User | Count |
---|---|
53 | |
34 | |
17 | |
17 | |
15 |