topic year over year Sales Calculation not working in DAX Commands and Tips

year over year Sales Calculation not working

Anonymous — Mon, 04 Jul 2022 15:36:24 GMT

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!

Re: year over year Sales Calculation not working

Jihwan_Kim — Mon, 04 Jul 2022 17:14:35 GMT

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

Re: year over year Sales Calculation not working

Anonymous — Mon, 04 Jul 2022 17:29:26 GMT

Hey!

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

Re: year over year Sales Calculation not working

Jihwan_Kim — Mon, 04 Jul 2022 17:42:57 GMT

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.

Re: year over year Sales Calculation not working

Anonymous — Tue, 05 Jul 2022 14:20:24 GMT

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!

Re: year over year Sales Calculation not working

Jihwan_Kim — Tue, 05 Jul 2022 19:30:22 GMT

Hi,

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

I created a measure, instead of calculated column.

Thank you.

Re: year over year Sales Calculation not working

Anonymous — Tue, 05 Jul 2022 20:34:44 GMT

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

Re: year over year Sales Calculation not working

Jihwan_Kim — Wed, 06 Jul 2022 03:34:13 GMT

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.