Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Cal sale between dates

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

 

Need Help
  • amitchandak's avatar
    amitchandak
    5 years ago

    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's avatar
    Anonymous
    5 years ago

    Hi amitchandak, 

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

     

     

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

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

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi amitchandak, 

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

       

       
    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

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

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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.