cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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 a 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 a 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 a 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
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

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

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors