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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
NewbieJono
Post Partisan
Post Partisan

Previous Week a month Month and Year earlier

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.

 

Capture1.PNG

 

 

1 ACCEPTED SOLUTION

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



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
I will not give you bad advice, even if you unknowingly ask for it.

View solution in original post

6 REPLIES 6
v-yilong-msft
Community Support
Community Support

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

 

NewbieJono
Post Partisan
Post Partisan

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

 

Capture100.PNG

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



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
I will not give you bad advice, even if you unknowingly ask for it.
v-yilong-msft
Community Support
Community Support

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.

 

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

MattAllington
Community Champion
Community Champion

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



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
I will not give you bad advice, even if you unknowingly ask for it.

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.