Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
miraglia
Advocate I
Advocate I

Timeintelligence DAX by Week previous Year not showing totals

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

 

 

Bildschirmfoto 2021-03-24 um 10.20.01.png

2 ACCEPTED SOLUTIONS

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

View solution in original post

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

)

)

View solution in original post

4 REPLIES 4
amitchandak
Super User
Super User

@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

 

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

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

)

)

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors