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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Rubenvw
Advocate I
Advocate I

Problem when presenting sameperiodlastyear data based on weeknumbers

I have a problem in DAX using the SAMEPERIODLASTYEAR function when presenting the data based on weeknumbers. I am struggling with this for more than 10 hours and still did not found the cause of the problem..:(

 

I am having a cumulative sales measure (year to date):

 

YTDSales = CALCULATE(SUM(Sales[SalesAmount]);DATESBETWEEN(Date[Date];DATE(YEAR(MAX(Date[Date]));1;1);MAX(Date[Date])))

 

The measure for creating the 'Sales Previous Year':

 

SalesPreviousYear = CALCULATE([YTDSales];SAMEPERIODLASTYEAR(Date[Date]))

 

My date table is dynamic, based on SalesDates (minSalesDate and maxSalesDates are variables):

 

CALENDAR(DATE(YEAR(minSalesDate);MONTH(minSalesDate);DAY(minSalesDate));DATE(YEAR(maxSalesDate);MONTH(maxSalesDate);DAY(maxSalesDate)))

Everything works fine when I present the data within a table using the columns Date, YTDSales, SalesPreviousYear. When I replace the date column for weeknumber("ww") and set a report filter on year 2017, it does not show the right value for the previous year. The date table is generated until 01-06-2017 and the incorrect value is for weeknumber 22. I think it show a value that should be presented around week 25/26/27.

 

Does anyone know the cause of the problem that it does not show the correct data for the previous year when I present the data per weeknumber?

 

Thank you very much.

1 ACCEPTED SOLUTION

 

The inbuilt time intelligence functions must use the date column. If you want to use anything else (like a week number) you must use custom time intelligence.  I explain that here http://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.

View solution in original post

4 REPLIES 4

Is there any reason why you can't use totalytd() to calculate your YTD number?  That is the first thing I would try



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

Thanks for your reply @MattAllington

 

It is a good point, I will change it. But it does not solve my problem:(

 

The inbuilt time intelligence functions must use the date column. If you want to use anything else (like a week number) you must use custom time intelligence.  I explain that here http://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.

Great. I think this should be the solution.

 

After reading your blog I do understand the Time Intelligence better and  I have figured out a solution (using dateadd). 

 

Thanks.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.