Forum Discussion

Kate47537's avatar
Kate47537
Frequent Visitor
3 years ago
Solved

Formula Help

Hi,   I am trying to calculate products sales based on a 3 year (vitality date) (meaning the product has a start date and we measure sales from that date 36 months forward and then we stop tracking...
  • tamerj1's avatar
    tamerj1
    3 years ago

    Hi Kate47537 

    please try

    NewMeasure =
VAR CurrentDate =
    MAX ( 'Calendar'[Date] )
RETURN
    SUMX (
        'Daily Sales',
        IF (
            CurrentDate < EOMONTH ( 'Daily Sales'[Marketing Intro Date], 36 ),
            'Daily Sales'[Product Sales],
            0
        )
    )
Kate47537's avatar
Kate47537
Frequent Visitor
3 years ago

I was trying to build using the below if Statement:

 

I have the follwoing fields:  Product Sales, Marketing Intro Date, and Calendar Day

 

If(today()<EOMONTH(Daily Sales[Marketing Intro Date],36), Daily Sales[Product Sales],0)

but I believe I am missing the reference to Calendar Day.  Because what I would like is adjusted sales to return with product sales for any marketing intro date with a date greater then 36 months only.

the adjusted sales would be only the items we sold within that 36 month time frame.

 

  • Kate47537's avatar
    Kate47537
    Frequent Visitor
    3 years ago

    IN addition this is the formula I use in excel that works.

    =IF(DATEVALUE(Calendar Day)<EOMONTH(DATEVALUE(Marketing Intro Date),36),Product Sales,0)