topic Previous Week a month Month and Year earlier in DAX Commands and Tips

Previous Week a month Month and Year earlier

NewbieJono — Wed, 12 Jun 2024 17:44:02 GMT

Hello, is there a method to get these results with DAX based on one slicer. what I need it the results of a week from four weeks ago and the same week a year ago.

I have a date table set up with the week commencing field. I can easily get the green section, but I am not sure about the Dax to get the orange, if its possible.

Re: Previous Week a month Month and Year earlier

MattAllington — Wed, 12 Jun 2024 20:51:30 GMT

Yes. It's called time intelligence and you can read about it here https://exceleratorbi.com.au/dax-time-intelligence-beginners/

Re: Previous Week a month Month and Year earlier

Anonymous — Thu, 13 Jun 2024 02:10:18 GMT

Hi NewbieJono ,

I think you can try the following DAX code, it might be able to give you some help.

4 Week Before = CALCULATE ( [YourMeasure], DATEADD ( 'DateTable'[WeekCommencing], -4, WEEK ) )

1 Year before = CALCULATE ( [YourMeasure], SAMEPERIODLASTYEAR ( 'DateTable'[WeekCommencing] ) )

From your screenshot I think there is some difficulty in trying to reproduce the results you want, if you can provide the .pbix file or more data I will reproduce the desired results for you.

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Previous Week a month Month and Year earlier

NewbieJono — Thu, 13 Jun 2024 08:04:09 GMT

Thanks for your help with this. The WEEK bit seems to error?

Re: Previous Week a month Month and Year earlier

NewbieJono — Thu, 13 Jun 2024 08:19:13 GMT

Any ideas on why this does not bring back volumes? There are volumes last year

Re: Previous Week a month Month and Year earlier

MattAllington — Thu, 13 Jun 2024 09:06:58 GMT

You are incorrectly using inbuilt time intelligence. Read the rules here

https://exceleratorbi.com.au/power-bi-calendar-tables/

you must have a calendar table at the day level of granularity. Your measure refers to the week ending date. It should be calendar date in a calendar table at the day level

Re: Previous Week a month Month and Year earlier

Anonymous — Thu, 27 Jun 2024 08:42:43 GMT

Hi NewbieJono ,

Have you solved your problem? If so, can you share your solution here and mark the correct answer as a standard answer to help other members find it faster? Thank you very much for your kind cooperation!

Best Regards

Yilong Zhou