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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Previous Year Measure completely wrong

Hi All - I am pulling my hair out with overcoming this basic requirement that other BI tools have handled so well.

 

The requirement is that users can interact with an embedded date slicer and control which date range to display a measure for. In this case it would be 'Sales'. There is also a requirement to shows last year sales for the same date range -1 year.

 

So I set it up my measure being 

Booked Sales = SUM(vw_fact_booking[net_total_amt])

and my date slicer works fine:

measure with date slicer works

So now to do 'Year ago' - easy enough:
Booked Sales YAGO = CALCULATE(SUM(vw_fact_booking[net_total_amt]),PREVIOUSYEAR(vw_fact_booking[booking_date]))


Nope! You can see that the sales for previous year are more like this:

 

 

What am I doing wrong?

 

 

 

 

 

2 ACCEPTED SOLUTIONS

Figured it out!

 

Took me a while to download the 800MB model over my phone 😮

 

The correct DAX formula should be

 

Booked Sales YAGO = CALCULATE(SUM(vw_fact_booking[net_total_amt]),SAMEPERIODLASTYEAR(dates[date]))

 

The PREVIOUSYEAR calc was taking the whole previous year into account even if you were only selecting a month from the current year.  Useful for somethings, but not for you.


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

Anonymous
Not applicable

Thank you @Phil_Seamark

Something so simple turning my hairs grey.

Appreciate your help mate,

Jake

View solution in original post

10 REPLIES 10
Phil_Seamark
Employee
Employee

Hi @Anonymous

 

Just wondering if you have gaps in dates in your data.

 

I strongly recommend you use the pattern of adding a date/calender table to your model.  A quickfire way to do this is using the following DAX calculate table statement.

 

dates = CALENDARAUTO()

 

When you have a date table, create a relationship between the [date] column and your vw_fact_booking[booking_date] column.

 

Change your measure to use the date column in the Date table and use the column from the date table in your slicer.

 

Does that make sense?

 

 

 

 

Create a relationship


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Anonymous
Not applicable

HI @Phil_Seamark, what if we have multiple dates that we want to analyse ?

I.E. we have booking_date and arrival_date and there is a need to look at both in same reports. However, I noticed that PowerBI only allows one date relationship at a time?

 

I tried your suggestion anyway, and it's still broke.

 

 

Booked Sales YAGO = CALCULATE(SUM(vw_fact_booking[net_total_amt]),PREVIOUSYEAR(dates[Date]))

 

 

 

 

 

Hi @Anonymous

 

You can have multiple relationships between your sales and date table.  Only one can be active.  Active just means default, in that you can write minimal DAX code that assumes the rules of the active relationship.  You can still analyise using the other dates, only you need to write your calculations to specify which of the relationships you'd like you use.

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Anonymous
Not applicable

Hi @Phil_Seamark - please see my edit to last post 

Hi @Anonymous

 

Do you mean about multiple relationships? 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Anonymous
Not applicable

@Phil_Seamark, nope, I tried your suggestion with the date table and the problem still remains 

Hi @Anonymous

 

Something odd is happening.

 

Are you able to provide me with a stripped down version of your PBIX file?

 

Save a copy and remove all tables other than your sales table and the date table.  Remove all columns from your sales table and upload to this post (if that is ok).  That might make it easier for me to spot what is going on.

 

 

 

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Figured it out!

 

Took me a while to download the 800MB model over my phone 😮

 

The correct DAX formula should be

 

Booked Sales YAGO = CALCULATE(SUM(vw_fact_booking[net_total_amt]),SAMEPERIODLASTYEAR(dates[date]))

 

The PREVIOUSYEAR calc was taking the whole previous year into account even if you were only selecting a month from the current year.  Useful for somethings, but not for you.


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Anonymous
Not applicable

HI Mate,

 

Could you suggest which would be a best practice to choose between(previous year vs sameperiodlastyear) to calculate the sale for Last year? bit Confused.

 

Regards,

RSD

Anonymous
Not applicable

Thank you @Phil_Seamark

Something so simple turning my hairs grey.

Appreciate your help mate,

Jake

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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