topic Re: How to get Previous Year sales value for complete year ? in DAX Commands and Tips

How to get Previous Year sales value for complete year ?

saurabhtd — Wed, 01 Mar 2023 16:36:03 GMT

Hello everyone,

I need help in DAX logic. I need dax calculation logic for calculating sum(Salesorder[salesinlocalcurrency]) for complete previous year i.e from 1st jan till 31 dec. The result I should be able to filter with 'calendar'Month filter/slicer.

Eg : when Year = 2017 is selected in filter then I am looking for sum(Salesorder[salesinlocalcurrency]) for dates all starting between 1 st jan 2016 till 31 st Dec 2016. The result of measure used for above calculation should filter based on Month Filter/slicer. i.e when I select different months it should chnage the value and give correct result for selcted month.

I tried some ways, these below dax are two of the ways I tried getting required result.

1. previous year sales = CALCULATE(SUM(salesorder[salesinlocalcurrency]),DATEADD('calendar'[Date],-1,YEAR))

This gives YTD previous year but I want complete previous year.

2. previous year sales1 = CALCULATE(SUM(salesorder[salesinlocalcurrency]),DATESYTD(ENDOFYEAR(DATEADD('calendar'[Date],-1,YEAR)),"12/31"))

This formula gives correct result but I cannot filter the result based on calendar'Month' which I want.

I am attaching sample dataset and PBI file for your reference.
Some information about Dataset : - Sales attribute is coming from a salesorder fact object. and year , month attributes are coming from calendar diamension object. salesorder and calendar tables have many to one relationship. The dataset has star schema.

Please help I tried many ways but I unable to fulfill this requirement. Thank for reading this. Thanks in advance for your help.

Sample Datamodel PBI file

amitchandak Sahir_Maharaj Greg_Deckler FreemanZ Ashish_Mathur olgad tamerj1 andhiii079845

Re: How to get Previous Year sales value for complete year ?

andhiii079845 — Wed, 01 Mar 2023 12:37:55 GMT

What is the problem? The filter requirment? Do you have a example file or some screenshot from the data, model.
If you choose 2019, you want to see 2019 and 2018?

Re: How to get Previous Year sales value for complete year ?

saurabhtd — Wed, 01 Mar 2023 12:42:33 GMT

Yes. Yes the answer of that measure should change based on filter. When I choose 2023 from year filter. Current year measure should show 2023 sales value. and Previous year should show 2022 complete year sales value

Re: How to get Previous Year sales value for complete year ?

andhiii079845 — Wed, 01 Mar 2023 12:50:15 GMT

Without the .pbix or further information about the data model and involved tables it is very diffcult to explain every step for me.

Re: How to get Previous Year sales value for complete year ?

saurabhtd — Wed, 01 Mar 2023 13:58:00 GMT

Sorry I cant share .pbix due to internal policies. But I can give information about data model. Sales attribute is coming from a salesorder fact object. and year , month attributes are coming from calendar diamension object. salesorder and calendar tables have many to one relationship. The dataset has star schema.

Re: How to get Previous Year sales value for complete year ?

andhiii079845 — Wed, 01 Mar 2023 14:01:04 GMT

Yes, you use the year slicer from the year dimension table, right?
Please share your DAX formula and involved columns for the bar chart. Is this possible?

Re: How to get Previous Year sales value for complete year ?

saurabhtd — Wed, 01 Mar 2023 14:16:03 GMT

I used year filter from calendar diamension table.

For finding current year sales the below formuala is meeting my requirements. I can filter this based on calendar'Month'

Current year sales = CALCULATE(SUM(salesorder[salesinlocalcurrency]),'calendar'[Year] = SELECTEDVALUE('calendar'[Year],YEAR(TODAY())))

But for finding previous year sales I have tried many ways

1. previous year sales = CALCULATE(SUM(salesorder[salesinlocalcurrency]),DATEADD('calendar'[Date],-1,YEAR))

This gives YTD previous year but I want complete previous year.

2. previous year sales1 = CALCULATE(SUM(salesorder[salesinlocalcurrency]),DATESYTD(ENDOFYEAR(DATEADD('calendar'[Date],-1,YEAR)),"12/31"))

This formula gives correct result but I cannot filter the result based on calendar'Month' which I want.

Re: How to get Previous Year sales value for complete year ?

FreemanZ — Wed, 01 Mar 2023 14:36:40 GMT

hi saurabhtd

Seems doable. Please abstract your case and reproduce it with simplified sample data. That would be very helpful for those who might be able to help you.

Re: How to get Previous Year sales value for complete year ?

andhiii079845 — Wed, 01 Mar 2023 16:25:32 GMT

I agree build a simple .pbix fille which show us the problem.

Re: How to get Previous Year sales value for complete year ?

saurabhtd — Wed, 01 Mar 2023 16:35:27 GMT

Sample PBI file

Click on this you will be able to download the file. The problem is also explained in that file.

Re: How to get Previous Year sales value for complete year ?

andhiii079845 — Wed, 01 Mar 2023 17:09:20 GMT

I check the pbi file right now. Where is your month / year filter you mentioned in the description?
DO you want to filter with a slicer like this:

Re: How to get Previous Year sales value for complete year ?

saurabhtd — Wed, 01 Mar 2023 17:12:23 GMT

No. It will be filter like this.

Re: How to get Previous Year sales value for complete year ?

andhiii079845 — Wed, 01 Mar 2023 17:21:56 GMT

Okay, my fault. I think you mean slicer.
If years is selected: sum() privious year (complete year)
if month and year is selcted: sum() complete month in the last year?

Re: How to get Previous Year sales value for complete year ?

saurabhtd — Wed, 01 Mar 2023 17:40:04 GMT

Yes

Re: How to get Previous Year sales value for complete year ?

andhiii079845 — Wed, 01 Mar 2023 17:42:41 GMT

Perhaps try this:

Previous year sales fix =

Var _monthfilter = if(HASONEVALUE('calendar'[Month]),VALUES('calendar'[Month]),0)

VAR _yearfilter = INT(SELECTEDVALUE('calendar'[Year])-1)

VAR _functionyear = sumx(FILTER(ALL(Salesorder),RELATED('calendar'[Year])=_yearfilter),Salesorder[salesinlocalcurrency])

VAR _functionmonth = sumx(FILTER(ALL(Salesorder),RELATED('calendar'[Year])=_yearfilter && RELATED('calendar'[month])=_monthfilter),Salesorder[salesinlocalcurrency])

RETURN if(_monthfilter>0,_functionmonth,_functionyear)

Re: How to get Previous Year sales value for complete year ?

saurabhtd — Wed, 01 Mar 2023 18:04:50 GMT

Thank you. This works perfectly I checked. Thank you again for helping. Is there any much more efficient logic than this possible ? just asking because it is taking significant time to load when change filter.

Re: How to get Previous Year sales value for complete year ?

andhiii079845 — Wed, 01 Mar 2023 18:11:53 GMT

It depands what kind of machine you have and how many data you have in the dataset. In my case with the sample file it is very fast (less then 0,5 sec)

Re: How to get Previous Year sales value for complete year ?

saurabhtd — Wed, 01 Mar 2023 18:17:04 GMT

yeah. Right. I was checking at my actual dataset. The actual dataset is big that I had not shared because of policies. There it is taking to much time to load when I filter with month/year. That's why I asked is there any optimized dax also possible ?

Re: How to get Previous Year sales value for complete year ?

andhiii079845 — Wed, 01 Mar 2023 18:17:09 GMT

Good question, I am not so good in DAX to know how to optimize it. 😞

Re: How to get Previous Year sales value for complete year ?

saurabhtd — Wed, 01 Mar 2023 18:19:22 GMT

Not an issue. Thank you helped in realising this logic. I had tried a lot but was unable to figure.