Prior Year Sales data FULL year when current year date selected

DAX DAX Calculations
1

Greetings all,

I am new to this forum and looking to get some insight on an issue I can’t seem to figure out. What I would like to see in a simple table format is by Customer, total sales for the current year values by filtering the Year and Quarter, but then also show as a column all of Previous Years sales for those customers.

So, if I filter 2021 and Q1, show all Sales data for Q1-2021 and then have a column that shows all of 2020 sales for that customer. I’m thinking it needs to be with a CALCULATE and FILTER function but can’t seem to get it.

Appreciate the help in advance…

Chuck O

2

Hi,

If all the sales data are in a single table, you can try using measures and leveraging variable in your calculation.

Y-1 Sales =

VAR CurYr = SELECTEDVALUE( ‘Table’[Year] )
VAR PrevYr = CurYr - 1

Calculate(SUM(‘Table’[Sales]), Filter(All(‘Table’),‘Table’[Year] = PrevYr))

Hope this make sense.

To better visualize this, perhaps you could create a small sample dataset and PBIX file that you can illustrate the issue.

3

@cobrecht,

Welcome to the forum - great to have you here!

You will always get the best response/support on the forum if you provide not only a clear explanation of your issue, but a PBIX work in progress and a mockup of the results you want to see.

If your data are sensitive, we have instructions for masking confidential info, as well as multiple free tools to create datasets and data models representative of your problem:

  • Brian
4

Hello @cobrecht

Would you be able to send sample .pbix file?
That will really help you find a quick solution.

Regards
Kumail Raza

5

Hi @cobrecht , we’ve noticed that no response has been received from you since the 26th of May.

We are waiting for the masked demo pbix file and any other supporting links and details. We may need this to help you further with your inquiry.

In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

6

Hi @cobrecht ,

Please receive hereby a solution on your request (other ways probably possible as well)
See also attached PBIX :
YTD and prev years v2.pbix (112.3 KB)

4 measures are created:

  1. Basic measure sales:

Total sales = SUM( 'SalesT'[Amount])

  1. Total cumulative sales

Cum7 Sales =
Var CumSales71 = CALCULATE( [Total Sales] ,
filter( ALLselected(DateT),DateT[Date] <= MAX(DateT[Date])))
return
if([Total sales]<>0, CumSales71, BLANK())

  1. YTD Sales, (can never be bigger as cum. sales, (time slicer selection possible)

YTD7 Sales =
Var YTD71 = CALCULATE( [Total Sales] , ALLselected(DateT), DATESYTD( DateT[Date] ))
//if the cumulative sales is smaller as YTD, due to time slicer, take cum sales
Var YTD71Adj = IF([Cum7 Sales] < YTD71, [Cum7 Sales], YTD71)
return
IF([Total sales] <> 0, YTD71Adj, BLANK())

  1. Sales previous years ( depending on the time slicer)

Previous yrs sales = [Cum7 Sales] - [YTD7 Sales]

YTD and previous years sales
YTD and previous years sales809×693 41.6 KB

Kind regards, deltaselect

1 Like
7

Hi @cobrecht, did the response provided by @BrianJ and @deltaselect help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query.

I hope that you are having a great experience using the Support Forum so far. Kindly take time to answer the Enterprise DNA Forum User Experience Survey, we hope you’ll give your insights on how we can further improve the Support forum. Thanks!

8

Hi @cobrecht, we’ve noticed that no response has been received from you since the 26th of May. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

9

Hi @cobrecht, due to inactivity, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box.

Kindly take time to answer the Enterprise DNA Forum User Experience Survey, we hope you’ll give your insights on how we can further improve the Support forum. Thanks!