topic Re: Calculating Sales for current year or month by products older than 5 years depending on date context in DAX Commands and Tips

Calculating Sales for current year or month by products older than 5 years depending on date context

Anonymous — Fri, 13 May 2022 05:13:52 GMT

Hi!
I've made a previous post about this but unfortunatley the problem remains and is not as dynamic as I need it to be.
I have a datasets that's around 3gb so will not be able to provide full dataset but can try to provide examples if necessary.

I have a Fact Table with sales and a Products table with released for salesdate. I need to create a dynamic way to filter products that have a ReleasedforSalesdate that is -5 years old in between (firstdate,Year,-5) & lastdate(firstdate,year, -5) of the sliced/selected calenderdate. I've tried to create a massive calculated column to act as a flag but have not been able to connect a dynamic filter to show the sales only for the selected period.

Calculated column is a link table between Fact and Product table:
CounterNSR= IF( ReleasedforSalesDate > [LastDateInMonth] -1826, 1,0)

SalesforProductslessthan5=

CALCULATE([Sales], [CounterNSR]=1)

NewSalesratio=

Calculate(DIVIDE( [NewSales], [TotalSales],0))

Expected result:
If I select year 2021 I want to see the invoiced sales for 2021 only for products that have a RFSD between 2016/01/01 - 2021/12/31. If I selected a month, (for e.g. March 2021) I only want to show products with a RFSD between 2016/03/01 - 2021/03/31.

Re: Calculating Sales for current year or month by products older than 5 years depending on date context

amitchandak — Fri, 13 May 2022 05:56:23 GMT

Anonymous , Not very clear

All 5 year sales

CALCULATE([Sales], Filter(all(Table), [CounterNSR]=1) )

Selected period sales = [Sales]

Select period sales only from 5 years

CALCULATE([Sales], Filter((Table), [CounterNSR]=1) )

Re: Calculating Sales for current year or month by products older than 5 years depending on date context

Anonymous — Fri, 13 May 2022 08:32:39 GMT

Hi amitchandak and thank you for replying.

I've tried that measure but it's not working, could it be due to my datamodel connections?
The table is returning sales for the products less than 5 year but returning sales alot larger than they should be. New sales is expected to be less than 20% but show 85% in current measure.

The calculated column is consists of XX million rows where i've taken each itemnumber per historic month and returned 1 if the released for sales date is less than 5 years (-1825 days) in that month or 0 if not.
The calculated column has an active connection to the sales table, an inactive to product (to bring in ReleasedforSalesdate) and an inactive to Date.

Could it be that the calender(orange) is not able to correctly iterate and filter Sales (Yellow) and NSRCounter (blue)?

My estimated solution is supposed to filter the products on ReleasedforSalesdate depending on the current datecontext in my slicer( 2021 or 2021-01) for example and filter products that are less than 5 years. Then I want to calculate the current sales for those prodiucts in my selected date context (e.g sales for 2021-01 for products XXX)

Re: Calculating Sales for current year or month by products older than 5 years depending on date context

daXtreme — Sat, 14 May 2022 10:33:17 GMT

// Let's assume that: // 1. Dates (your proper calendar) is connected by 1-* to Purchases (fact) // on the field Purchase[PurchaseDate]. // 2. Products is a dim table that connected to your fact by 1-* // on Products[ProductID]. // 3. Your Products has a field Products[Release Date]. This field is not // connected to your calendar. // Then you want to write a measure that will calculate // the sales in the current period of products that have Release Date // within N years (in your case N = 5) back from the currently // selected period of time. The measure should, of course, respond // to any other selections in any other dimensions. Bear in mind that // fact tables should always be hidden and slicing must always take // place via dimensions. The only exceptions to this is degenerate // dimensions. We'll make N a parameter in the measure. // // IF YOUR LOGIC WITH DATES IS DIFFERENT, PLEASE ADJUST ACCORDINGLY. [Purchase Amount (-N yrs)] = var YearsBack = 5 // change it accordingly var ShiftedDates = // For this func to work, the selected period of time // from the calendar must be contiguous. DATEADD( Dates[Date], -YearsBack, YEAR ) var ShiftedStartDate = MINX( ShiftedDates, Dates[Date] ) var ShiftedEndDate = MAXX( ShiftedDates, Dates[Date] ) var ProductsWithReleaseDateWithinNYearsBack = CALCULATETABLE( DISTINCT( Products[ProductId] ), KEEPFILTERS( Products[Release Date] >= ShiftedStartDate ), KEEPFILTERS( Products[Release Date] <= ShiftedEndDate ) ) var Result = CALCULATE( [Sales], ProductsWithReleaseDateWithinNYearsBack ) return Result