Formula Help

[Kate47537]

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 · Accepted solution]

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]

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]

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)

[amitchandak]

@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...

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.