Timeintelligence DAX by Week previous Year not showing totals

[miraglia]

"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!

[amitchandak · Accepted solution]

@miraglia ,

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))

[amitchandak]

@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))

[miraglia]

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

[amitchandak · Accepted solution]

@miraglia ,

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))

[miraglia · Accepted solution]

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

)

)