Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

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. 
3 REPLIES 3
daXtreme
Solution Sage
Solution Sage

 

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

 

 

Anonymous
Not applicable

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

Felix_Karlson_0-1652422469757.png

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)

 

amitchandak
Super User
Super User

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors