topic Calculating sum of Sales in a given period in DAX Commands and Tips

Calculating sum of Sales in a given period

LotteLaugesen — Mon, 30 Nov 2020 09:09:01 GMT

I need to calculate sales per product from Expected selling start date and 4 month ahead.

How do I do that?

I have tried this measure, but it gives me an error.

Volume 4 Month =

CALCULATE(SUM('Fact Sales'[Volume]),

USERELATIONSHIP(Dim_Calendar[Date],'New Products'[Expected Selling Start Date]),

DATESBETWEEN(Dim_Calendar[Date],'New Products'[Expected Selling Start Date],DATEADD('New Products'[Expected Selling Start Date],4,MONTH)))

Can anybody help me? I added a link to the test PBI file.

https://drive.google.com/file/d/1QUCDwDFptavE_Vcl_VV6wOwo_NCzEc1k/view?usp=sharing

Below is a picture of what I would like to get.

Newbie Lotte 😊

Re: Calculating sum of Sales in a given period

amitchandak — Mon, 30 Nov 2020 09:38:59 GMT

LotteLaugesen , not able to relate your comments with the with data, can you elaborate

Re: Calculating sum of Sales in a given period

LotteLaugesen — Mon, 30 Nov 2020 09:58:50 GMT

For each product in New Products file I want sum of Volume from Fact Sales for 4 month after Expected selling start date. I only want to list the products in the New Products file, not any other product sold.

For Product100 it should sum volume from Fact Sales from 1st of January to end of 1st of April.

For Product200 it should sum volume from Fact Sales from 2nd of February to end 2nd of May

And so on.. Does this make it more clear?

Re: Calculating sum of Sales in a given period

amitchandak — Mon, 30 Nov 2020 10:09:21 GMT

LotteLaugesen , Try like

Volume 4 Month =
CALCULATE(SUM('Fact Sales'[Volume]),
USERELATIONSHIP(Dim_Calendar[Date],'New Products'[Expected Selling Start Date]),
DATESINPERIOD(Dim_Calendar[Date ],Min(Dim_Calendar[Date])+1,4,MONTH) )

Re: Calculating sum of Sales in a given period

LotteLaugesen — Mon, 30 Nov 2020 10:16:38 GMT

Thank you for helping, but the measure shows a blank result.

Re: Calculating sum of Sales in a given period

amitchandak — Mon, 30 Nov 2020 13:21:58 GMT

LotteLaugesen , Try this

CALCULATE(CALCULATE(SUM('Fact Sales'[Volume]),
USERELATIONSHIP(Dim_Calendar[Date],'New Products'[Expected Selling Start Date])),
DATESINPERIOD(Dim_Calendar[Date ],Min(Dim_Calendar[Date])+1,4,MONTH) )

Re: Calculating sum of Sales in a given period

LotteLaugesen — Mon, 30 Nov 2020 13:47:04 GMT

I have changed the measure to this:

Volume 4 Month =

CALCULATE(SUM('Fact Sales'[Volume]),

DATESINPERIOD(Dim_Calendar[Date],MIN('New Products'[Expected Selling Start Date]),4,MONTH))

And now I get the corrrect result.
Thanks for your help 😊