Slicer Selection to show/hide column values & corresponding counts

[SahityaYeruband]

Hi Everyone,

I am required to build a matrix visual containing, for eg :  Country, Dept, Product, Sales.
Slicers are given on Month & year of sales.

Each Product has a introdution date. Sales are captured from the date of introduction. However the sales shouldn't be shown unless its after a month of Product Introduction. 

Country	Category	Product	Introduction Date	Slicer Selected by User
USA	Beauty	Foundation	23/05/2024	10-2024	Should show in Visual
USA	Hair Care	Blow Dryer	08/08/2024	8-2024	Shouldn't show in Visual
USA	Hair Care	Blow Dryer	08/08/2024	9-2024	Should Show in Visual
USA	Beauty	Foundation	23/05/2024	4-2024	Shouldn't show in the Visual

Appreciate your help in advance.

Thanks,

Sahitya Y

[Kedar_Pande]

@SahityaYeruband 

Create a measure that checks the condition and filters data dynamically:

Show Sales =
IF(
DATEDIFF(
MIN('Table'[Introduction Date]),
EOMONTH(SELECTEDVALUE('Date'[Date]), 0),
MONTH
) >= 1,
SUM('Table'[Sales]),
BLANK()
)

Use Show Sales as the value in the matrix visual.
Add slicers for month and year to filter data dynamically.

💡 If this helped, please give Kudos 👍 or mark it as a Solution ✅.
Best regards,
Kedar
🌐 Connect on LinkedIn

[ValtteriN · Accepted solution]

Hi,

I am not sure if I follow, but here is how to show selection based on slicer.

Measure to count = 

COUNTROWS('Table (48)')

test to show values =

var _slicerD = MAX(DimDate[Date]) RETURN

IF(MAX('Table (48)'[Introduction Date])+30< _slicerD,1,0)

Apply test as a filter:

Now when September is selected all values are shown since 30 days after 8.8 is larger than 30.9. The critical date is 7.9.2024 since 30+8.8 < 7.9. 

So if 7.9 is selected the products introduced at 8.8 are not shown:

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

[bhanu_gautam]

@SahityaYeruband , First Create a Calculated Column for Introduction Month

IntroductionMonthYear = FORMAT([Introduction Date], "YYYY-MM")

Create a Measure to Check if Sales Should be Shown:

ShowSales =
VAR SelectedMonthYear = SELECTEDVALUE('SlicerTable'[MonthYear])
VAR IntroductionMonthYear = FORMAT([Introduction Date], "YYYY-MM")
VAR IntroductionPlusOneMonth = FORMAT(EDATE([Introduction Date], 1), "YYYY-MM")
RETURN
IF(SelectedMonthYear >= IntroductionPlusOneMonth, 1, 0)

Use the measure ShowSales to filter the matrix visual. Only show rows where ShowSales is 1.