How to get Previous Year sales value for complete year ?

[saurabhtd]

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 

[FreemanZ]

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. 

[andhiii079845]

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

[saurabhtd]

Sample PBI file  

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

[andhiii079845]

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:

 

[saurabhtd]

No. It will be filter like this. 

[andhiii079845]

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?  

[andhiii079845 · Accepted solution]

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)

[saurabhtd]

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. 

[andhiii079845]

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)

[saurabhtd]

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 ? 

[andhiii079845]

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

[saurabhtd]

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

[saurabhtd]

Yes

[andhiii079845]

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

[saurabhtd]

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. 

[andhiii079845]

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?

[saurabhtd]

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. 

[andhiii079845]

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? 

[saurabhtd]

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