Measure to bring previous year total sales?

[jd8766]

Hi everyone, I have a table that looks like the below.. it's a basic sales table which shows sales, sale date, order date and other information such as product and sale channel..

What I want to build is a matrix that shows me the product, sales channel and then the number of sales for the selected sale date period. This part is easy and I have already done it... however I then want to add another figure to show me the TOTAL sales in the previous 12 months by the [OrderDate], split by the product and the sales channel.. something like this..

So the PrevYearTotal will be a count of the sales prior to the end of the previous [OrderDate] month (so if I looked at it today, it would show the total sales with an [OrderDate] between 30/04/2021 and 30/04/2022. This figure will only ever change once we go into a new calendar month.

Can anyone advise the best way to achieve this?

[v-yalanwu-msft · Accepted solution]

Hi， @jd8766 ；

You could try it.

PrevYearTotal = CALCULATE(SUM('Table'[Salekey]),FILTER(ALL('Table'),[OrderDate]<=EOMONTH(TODAY(),-1)&&[OrderDate]>EOMONTH(TODAY(),-13)))

The final show:

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[amitchandak]

@jd8766 , Join both of them with same date table. and use date/period in visual/slicer from the date table 

Assume the order date is inactive 

calculate( calculate( SUM(Sales[Sales Amount]),USERELATIONSHIP ('Sales'[Order Date], 'Date'[Date])),DATESYTD(dateadd('Date'[Date],-1,Year)))

or

calculate( calculate( SUM(Sales[Sales Amount]),USERELATIONSHIP ('Sales'[Order Date], 'Date'[Date])),previousyear('Date'[Date]))

example

https://radacad.com/userelationship-or-role-playing-dimension-dealing-with-inactive-relationships-in...

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...