topic Re: Formula Help in DAX Commands and Tips

Formula Help

Kate47537 — Tue, 18 Oct 2022 22:55:11 GMT

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

Re: Formula Help

amitchandak — Wed, 19 Oct 2022 02:00:49 GMT

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.

Re: Formula Help

Kate47537 — Wed, 19 Oct 2022 10:17:01 GMT

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.

Re: Formula Help

Kate47537 — Wed, 19 Oct 2022 10:19:44 GMT

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)

Re: Formula Help

tamerj1 — Wed, 19 Oct 2022 10:37:30 GMT

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