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 sales, I cannot seem to get the product sales to adust based on the 36 month window from start date, we do however want the product sales to aggregate, meaning we want current year to allow fall off of expired materials (each month) but allow the any new materials to come on well product sales.

 

Thank you

Kate

  • 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
        )
    )

4 Replies

  • tamerj1's avatar
    tamerj1
    Community Champion
    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
        )
    )
  • amitchandak's avatar
    amitchandak
    Super User
    3 years ago

    Kate47537 , Seem like you need sum data based on sales date. If you have date table joined already with first date you can join(inactive) sales date also and use userelationship in measure

     

    https://radacad.com/userelationship-or-role-playing-dimension-dealing-with-inactive-relationships-in-power-bi

     

    The information you have provided is not making the problem clear to me. Can you please explain with an example.
    Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
    Appreciate your Kudos.

  • 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)