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
jonathandhay
Frequent Visitor

Weekly Index Year Ago

I have a few years of data that come in weekly.  I want to create an Index Year Ago Column (100* Current Week value /52 Weeks Ago value).  I've seen that the time intelligence functions work with days but not weeks.

 

I tried to create and link a date table (connected via the weekly date). Then I tried to pull the week that is offset by 52 weeks.  This is not working. 

Last Week Date Test =
CALCULATE([CovidMetricSum],
FILTER(ALL('Date'),
'Date'[WeekOffset]=SELECTEDVALUE('Date'[WeekOffset])-52))

 

jonathandhay_1-1633479661000.png

Let me clarify if the week 9/21/2019 has a value of 2,000,000 and 52 weeks later the week ending 9/19/20 has a value of 2,200,000 I want the IYA in the 9/19/20 line to be 110. I don't want to base this on days because some years have 366 days and some 365.

 

1 ACCEPTED SOLUTION

@jonathandhay  Please @ mention me in your next reply so I don't lose the thread.

 

Taking the MAX of your Date table will provide the DAX with the context of the current date in the table visual (you must use your Date table date in all your visuals and your DAX). 

 

Make sure you:

1) Mark date table as Date table.

2) Use Date table date in the visual

3) See attached file below my signature of this post - you have a few options depending on which value you want to match to the week.

 

OPTION A - Last Year value using standard time intelligence measure and the End of Week column from Date table

 

Previous Year CovidMetricSum =
CALCULATE([CovidMetricSum], DATEADD(DimDate[Date], -1, YEAR))
 

AllisonKennedy_0-1634110199324.png

 

OPTION B: Use Week of Year Number:

AllisonKennedy_1-1634110247999.png

 

 

 

 


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

View solution in original post

8 REPLIES 8
amitchandak
Super User
Super User

@jonathandhay , Last year same week day is 364 days behing

with help from date table

Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-364,Year))

 

 

Other option with week and week rank

 

 

or

 

This year week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year]) && 'Date'[Week] = Max('Date'[Week]) ))
Last year same week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year])-1 && 'Date'[Week] = Max('Date'[Week])))

 

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

When I tried your Dax I'm not getting last years values, I'm still getting the value from the current week?I'm not sure what's going wrong?

jonathandhay_1-1633517982768.png

Similarly, the other function which ties back to my date table only put one value in for the year and it was same weeks value instead of the value year ago

jonathandhay_2-1633518360373.png

 


@amitchandak wrote:

@jonathandhay , Last year same week day is 364 days behing

with help from date table

Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-364,Year))

 


@amitchandak  Won't this give the data for 364 years ago???

 

@jonathandhay  Are you wanting the average weekly value? (I'm guessing this from your explanation being divided by 52: 

 (100* Current Week value /52 Weeks Ago value)

 

If so, you can use DATESINPERIOD and you don't need to worry about week time intelligence, just providing the Week Number from the Date Table inside the visual will do it.

 

WeeklyAvgRolling12MonthsMEASURE = CALCULATE ( [CovidMetricSum] , DATESINPERIOD( MAX(DimDate[Date]), -1, YEAR) ) / 52

 

https://dax.guide/datesinperiod/ 


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Thank you for the reply.  Since my dates span several years, I'm not sure what taking the max of my date table is going to do?  Let me clarify.  For every row, or date in the table, i want to take the sales in that week and divide by the same week year ago (Week Offset of -52 weeks).  I don't know how to do this offset. I do have a date table, but my raw data is coming in weekly so I matched this to the week ending date in my Date table.  However, I still don't know what to do next.  Sorry I'm very new to PBI. 

@jonathandhay  Please @ mention me in your next reply so I don't lose the thread.

 

Taking the MAX of your Date table will provide the DAX with the context of the current date in the table visual (you must use your Date table date in all your visuals and your DAX). 

 

Make sure you:

1) Mark date table as Date table.

2) Use Date table date in the visual

3) See attached file below my signature of this post - you have a few options depending on which value you want to match to the week.

 

OPTION A - Last Year value using standard time intelligence measure and the End of Week column from Date table

 

Previous Year CovidMetricSum =
CALCULATE([CovidMetricSum], DATEADD(DimDate[Date], -1, YEAR))
 

AllisonKennedy_0-1634110199324.png

 

OPTION B: Use Week of Year Number:

AllisonKennedy_1-1634110247999.png

 

 

 

 


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

If your Date table has both a WEEKNUM and YEAR column, you can calculate your measure where Year = Year -1 and Weeknum values match.

 

Pat

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


This is exactly what I'm trying to learn how to do in Dax.  Do you have a code snipit?  I don't know how to say grab the value where the year is year -1 same week.

HI @jonathandhay,

I suppose his formula may be like this, you can take a look at the below formula if it helps:

 

measure =
VAR currDate =
    MAX ( Table[Date] )
RETURN
    CALCULATE (
        'calculate expression',
        FILTER (
            ALLSELECTED ( Table ),
            YEAR ( [Date] ) = YEAR ( currDate ) -1
                && WEEKNUM ( [Date], 2 ) = WEEKNUM ( currDate, 2 )
        )
    )

 

Regards,
Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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.