Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
// 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
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)
@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) )
User | Count |
---|---|
42 | |
28 | |
24 | |
20 | |
16 |
User | Count |
---|---|
54 | |
35 | |
18 | |
18 | |
15 |