How to calculate sales same period last year using two dates order date and invoice date

[mmiljak]

Hi,

 I have been trying to solve this for sometime now and cannot get results when using two dates. I can for one date but not two.

I have tried adding 2 filter conditions in the calculate statement but to no evale.

I have the following tables;

dimOrderDate

dimInvDate

dimSales

in the dimSales table the full record of the sales is listed with sales amounts and both dates.

What I need is to be able to filter on both dates for last years sales amount. example of an output

Order date	invoiced date	Current amount	samePeriodLastYear amount
01/01/2017	10/20/2017	3,350	2,230

the same period last year dates are 

Order date	invoiced date	Current amount
1/1/2016	10/20/2016	2,230

SAMEPERIODLASTYEAR AMOUNT =
CALCULATE(dimSales[Total sales]
,FILTER(ALL(dimOrderDatesDates)
,dimOrderDatesDates[YEAR]= MAX(dimOrderDates[YEAR])-1)
,FILTER(ALL(dimInvDates)
,dimInvDates[YEAR]=MAX(dimInvDates[YEAR])-1)
,SAMEPERIODLASTYEAR(dimInvDates[Inv_DATE])
,SAMEPERIODLASTYEAR(dimOrderDatesDates[Order_DATE]))

any help would be greatly appreciated

Mike

[v-yuezhe-msft]

@mmiljak,

Assume that your dimSales table is as follows.


Create the following columns in your dimSales table.

Orderyear = YEAR(dimSales[Order date])

invoiceyear = YEAR(dimSales[invoiced date])

ordermonth = FORMAT(dimSales[Order date],"MM/DD")

invoicemonth = FORMAT(dimSales[invoiced date],"MM/DD")

samePeriodLastYear amount = CALCULATE(SUM(dimSales[sales]),FILTER(dimSales,dimSales[Orderyear]=EARLIER(dimSales[Orderyear])-1 &&dimSales[invoiceyear]=EARLIER(dimSales[invoiceyear])-1&&dimSales[ordermonth]=EARLIER(dimSales[ordermonth])&&dimSales[invoicemonth]=EARLIER(dimSales[invoicemonth])))

Regards,
Lydia

[mmiljak]

Thank you very much for getting back to me, Question do these new date fields need to be in the same table as sales?

I have 2 data tables for invoice and order dates that break down the dates to year, day, month, quater.....

The only one that I donot have is the month/day

Let me know.

Example;

dimInvDate

DIM_INVDATE_SEQ	INV_DATE	YEAR	JVYEAR	JVMONTH	TRFYEAR	TRFMONTH	MONTH	WEEK	WEEKDAY	QUARTER	JVQUARTER	SEASON	JVSEASON	DAY
291449	05-JUN-10	2010	2010	8	2010	2	6	23	7	2	3	3	SU	156
291450	31-MAY-10	2010	2010	7	2010	1	5	22	2	2	3	2	SU	151
291451	01-JUN-10	2010	2010	8	2010	2	6	22	3	2	3	3	SU	152
291452	02-JUN-10	2010	2010	8	2010	2	6	22	4	2	3	3	SU	153
291453	09-JUN-10	2010	2010	8	2010	2	6	23	4	2	3	3	SU	160
291454	10-JUN-10	2010	2010	8	2010	2	6	23	5	2	3	3	SU	161

[v-yuezhe-msft · Accepted solution]

@mmiljak,

Yes. You can create DAX to bring invoice date and order date into dimsales table.

Regards,
Lydia

[mmiljak]

I have one more question. since we use sum in the calculate expression I cannot get a total when I use a table. If I sum the calculated colunm the total is incorrect. How can I add a total to the colunm in the table?

[Ashish_Mathur]

Hi,

share your dataset and show the expected result.

[mmiljak]

Here is my table the last colunm is calculated using a DAX formula. You can see that there is no total below only the Quantity has.

If I sum the Last colunm the total is completly incorect you can see that it double sums some of the rows and the total is wrong.

So how do I get a total on this calculated colunm.

Here the formula

SAME_PERIOD_LAST_YEAR_RN = CALCULATE(sum(roomSales[QUANTITY]),FILTER(roomSales,roomSales[BOOK_YEAR]=EARLIER(roomSales[BOOK_YEAR])-1  && roomSales[STAY_YEAR]=EARLIER(roomSales[STAY_YEAR]) - 1 && roomSales[BOOK_MONTH]=EARLIER(roomSales[BOOK_MONTH]) && roomSales[STAY_MONTH]=EARLIER(roomSales[STAY_MONTH])))

Thank you for your help

Mike

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.

[mmiljak]

Thank you very much for all your help

Mike