Cal sale between dates

[Anonymous]

Hi experts, 

Understand there are numbers of treads related to topic, however, I can't find any to solve my issue. Thank you for any idea and advice. 

I want to calculate the total sell-in and sell-out during the OnCounterDate (-6M to +2M) 

My Measure:

(Fcst) Sell-in =

VAR EL = DATEADD(FAST[International OCD],2,MONTH)

VAR SL = DATEADD(FAST[International OCD],-6,MONTH)

RETURN

CALCULATE (

'Sell-in'[Qty Shipped],

FILTER (

'Sell-in',

'Sell-in'[Shipment Date] <= EL

&& 'Sell-in'[Shipment Date] >= SL

)

)

No result for this measure

[amitchandak · Accepted solution]

@Anonymous , replace the var with following

VAR EL = DATE(year(FAST[International OCD]),month(FAST[International OCD])+2,day(FAST[International OCD]))
VAR SL = DATE(year(FAST[International OCD]),month(FAST[International OCD])-6,day(FAST[International OCD]))

or try rolling with a date tbale

Rolling 6 till next 2 month = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(dateadd(Sales[Sales Date],2,month)),-6,MONTH))

or use 8

Rolling 8 till next 2 month = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(dateadd(Sales[Sales Date],2,month)),-8,MONTH))

[v-rzhou-msft]

Hi @Anonymous 

Could show me a sample like your three tables(Sell-In, Sell-Out, Fast)? This may make it easier for me to understand your requirement and measure above.

I think you need to add a value instead of a column in your latest measure.

Due to you use Fast[International OCD] directly in your measure, it shows an error to you.

Can you show me your calculate logic of your measure? What kind of result you want :SUM,AVG,MAX of 

'Sell-in'[Qty Shipped]? You can show me a screenshot of the result you want to me.

Or you can provide me with your pbix file from your Onedrive for Business.

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[amitchandak · Accepted solution]

@Anonymous , replace the var with following

VAR EL = DATE(year(FAST[International OCD]),month(FAST[International OCD])+2,day(FAST[International OCD]))
VAR SL = DATE(year(FAST[International OCD]),month(FAST[International OCD])-6,day(FAST[International OCD]))

or try rolling with a date tbale

Rolling 6 till next 2 month = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(dateadd(Sales[Sales Date],2,month)),-6,MONTH))

or use 8

Rolling 8 till next 2 month = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(dateadd(Sales[Sales Date],2,month)),-8,MONTH))

[Anonymous]

I also have an error when using DATE(YEAR,MOTH,DATE)

[Anonymous · Accepted solution]

Hi @amitchandak, 

Thank for 😀. 
what is the difference between my DATEADD function and the DATE function you are using. 

[Anonymous]

Im not sure where did go wrong. any advice would be greatly appreciated