topic Showing Products between two dates depending on Cal Filter in DAX Commands and Tips

Showing Products between two dates depending on Cal Filter

Anonymous — Mon, 11 Apr 2022 14:43:06 GMT

Hello!

I'm via a CalculatedTable trying to filter out products that are between 4-5years old by their "Released for Sales Date".

If I select a year (for e.g. 2021) I want to see products that have a RFSD between 2016/01/01 - 2016/12/31. If I selected a month, (for e.g. December 2021) I only want to show products with a RFSD between 2016/12/01 - 2016/12/31.

My measure seems to be working somewhat but is only filtering on the latest date in the report and is not being filtered by my calender selection.

I've tried to troubleshoot with no luck. My VAR calenderfilters and FilteredMonth are working. I have an inactive connection between Cal[Date] and ReleasedforSalesDate (cannot be active because I have an active between Cal and my main Fact Table).

Another picture for clarification. The ReleasedforSalesDate table in the bottom right is ignoring any date filtered and is only showing products between 4-5years old from my latest reportdate (2022/04/05).

My current Measure!

RFSD Outgoing( 4 - 5Y ) =
VAR startdate = dateadd( LASTDATE( Cal[Date] ), -72, MONTH ) 
VAR enddate = dateadd( LASTDATE( Cal[Date] ), -60, MONTH ) 
VAR Altenddate = dateadd( LASTDATE( Cal[Date] ), -61, MONTH ) 

RETURN
IF(
    [FilteredMonth] = TRUE(),
    IF(
        AND(
          'Product'[ReleasedForSalesDate] >= Altenddate,
          'Product'[ReleasedForSalesDate] < enddate
        ),
        1,
        0
    ),
    IF(
        AND(
          'Product'[ReleasedForSalesDate] >= startdate,
          'Product'[ReleasedForSalesDate] < enddate
        ),
        1,
        0
    )
)

Any help or pointing me in the right direction would be apreciated!

Re: Showing Products between two dates depending on Cal Filter

Whitewater100 — Mon, 11 Apr 2022 15:45:14 GMT

Hi:

I'm not following 100%. You can have date discontinued in product table, you should be able create a column for "time discontinued". You can subtract the date INT(TODAY() - Date Discontinued. If it's more than 400 or 500 days you can add a column with this type indicator.

For example what result do you want to see for result when wanting to show products with a RFSD between 2016/12/01 - 2016/12/31? Can you show where that answer comes from? I'mguessing it's something like CALCULATE(COUNTROWS(Products, [Disc 4-5 yrs flag] > 0),
USERELATIONSHIP(Dates[Date], Products[ReleasedforSalesDate]))

or
var currdate = MAX(Dates[Date]

CALCULATE(COUNTROWS(Products, [Released for Sales Date] < currdate),
USERELATIONSHIP(Dates[Date], Products[ReleasedforSalesDate]))

I think your main question could be how to access the product table you can have USEREATIONSHIP or create a seond date table. E.G.

Re: Showing Products between two dates depending on Cal Filter

Anonymous — Mon, 11 Apr 2022 16:48:45 GMT

Thank you for giving this a shot. At this stage i'm only trying to get the filter to work. I'm using the calculated table to filter the visual via the built in function in PBI. From my limited understanding the filtering context is not working even though I have a connection to my calender and I can't understand why or how to get around it.

In the end I want a tablevisual with a list of the products between 4-5 years old in the sliced calender context.
Are you saying I need to to use a measure to filter out the products by USERELATIONSHIP and shouldn't use the PBI built in "Filter on this visual"?

My expected result is when I select 2021 in the Yearmonth slicer, I want the dates in my right "releasedforsalestable" (which is filtered on RFSD = 1) to only show the RFSD greater than startdate (2015-12-21) and less than enddate (2016-12-31). As of now it is showing the RFSD where startdate is 2016-04-05 and enddate is 2017-04-05 which is between 4-5years from my latest caldate (2022-04-05) instead of my filtered caldate (2021-12-31).

Is there anything I can provide to make this easier to understand? The report is 1,3G big so I can't share everything but I can create some sampledata if there's any specifics needed to clarify.

Re: Showing Products between two dates depending on Cal Filter

Whitewater100 — Mon, 11 Apr 2022 20:29:00 GMT

Hi:

I'm not sure if this is what you are looking for. I got my hands on some product data and have this to look at:

https://drive.google.com/file/d/1pQcm4OQ47UX1yJ8k_CmQFApxX6IjvZIK/view?usp=sharing

Today has been a little crazy and not much time to spend. I hope this helps somewhat..

Re: Showing Products between two dates depending on Cal Filter

Anonymous — Tue, 12 Apr 2022 09:29:42 GMT

Thank you! The solution was to filter via a measure instead of a calculated column.