year over year Sales Calculation not working

[Anonymous]

I have two tables. One Premium and One Dates. Here is the relationship: PaymentDate(Premium) and Date(Dates), it's a many to one relationship. 

I'm trying to calculate Year over Year Sales. Here is my formula so far, 

Total Sales Last Year = CALCULATE(SUM(Premium[TotalCollected]), SAMEPERIODLASTYEAR(Dates[Date]))

The current result so far, 

What am I missing here? It's throwing back same numbers as the current year. Any help is much appreciated!

[Jihwan_Kim]

Hi,

Please try, "assign your Dates Table as date table".

Hover over the mouse on your Dates Table's name, and right click. And then you can find a function called "Make as date table".

Set and use date tables in Power BI Desktop - Power BI | Microsoft Docs

[Anonymous]

Hey!

So I did do that. It is marked as date table with "date" field being the date column. 

[Jihwan_Kim]

May I ask what are your columns'  data type?

If possible, please share your pbix file's link here, and then I can try to look into it to come up with a more accurate solution.

Thanks.

[Anonymous]

here is a link to the pbix file, https://www.dropbox.com/s/zego0j370awlxoo/YearOVerYearTest.pbix?dl=0

I've cleaned the data up so it has only what you need. 

Thanks!

[Jihwan_Kim]

Hi,

Thank you for your message and please check the attached file.

I created a measure, instead of calculated column.

Thank you.

[Anonymous]

When I open up your file, it looks like its throwing the total collected numbers to future dates:

[Jihwan_Kim · Accepted solution]

Hi,

Thank you for your feedback.

Please kindly advise the below.

- Is the number correct?

- I simply used your calculatd column formula to create a measure. I did not change anything except your formula is for a calculated column and mine is for a measure. If your requirement is not to show the future dates information, please add some conditions into the measure. Please check the attached file that I amended the measure not to show the future date's information.