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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.