SEARCH function only working when the field is displayed

[jimbob2285]

I need the user to be able to select a date period for a sales table: Year-To-Date (YTD), Month-To-Date (MTD) & Week-To-Date (WTD) - But as MTD includes WTD and YTD includes MTD this can't simply filter on a column (see example below assuming we're currently in Week 3 of July):

Month	Week	Period
July	Week 1	MTD
July	Week 2	MTD
July	Week 3	WTD

as MTD (weeks 1 & 2) would exclude WTD (week 3) and as you can see MTD needs to include week 3

To overcome this I have:

• A calculated column in my sales table that assigns the appropriate period to each record by comparing the sales date with the current date and assigns "MTD/WTD" to week 3 (as opposed to just "WTD" in the example above)
• Created a Slicer table to hold the values for the Chiclet Slicer and (YTD, MTD & WTD)
• A measure (SelectedPeriod) in my sales table to search for the SELECTEDVALUE from the Slicer table in the Period calculated column in the Sales table

This seems to be working OK, but only when the SalesTable[Period] column is shown in the table visual - which is causing duplication, so where the table is showing sales by sales team for instance, adding the period column is duplicating sales teams for "MTD" & "MTD/WTD"

SelectedPeriod = VAR searchvalue=SEARCH(SELECTEDVALUE(SlicerTable[Period]), SELECTEDVALUE(SalesTable[Period]),,Blank())

RETURN

If(searchvalue>0,"Found")

Why is it only working when the Period column is shown in the table visual?

Any help would be appreciated as this is driving me crazy and I can't think of another way to achieve this... I don't want users to have to select both MTD & WTD to get MTD sales values

Thanks

Jim

[Anonymous]

Hi @jimbob2285,

Did tex628 's suggestions help with your scenario? if that is the case, you can consider Kudo or accept the helpful suggestions to help others who faced similar requirements to find it more quickly.

If these also don't help, please share more detailed information to help us clarify your scenario to test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[tex628]

Hi @jimbob2285 , 

I'd advice you to create 3 different measures instead. 

[WTD], [MTD] & [YTD] 

Then use a switch statement to swap between them depending on the slicer selection: 

Switch measure = 
SWITCH(
SELECTEDVALUE( SlicerTable[Period] )
"WTD" , [WTD] ,
"MTD" , [MTD] , 
"YTD" , [YTD] 
)

Br, 
J

[jimbob2285]

Hi, thanks for this, forgive my ignorance, I'm quite new to DAX, but I'm struggling to understand what the three measures would look like - For instance, for MTD I've tried an if statement to produce a 1 or 0, but that's not working 

Could you give me an example please

Here's a link to my example file: Example File 

Thanks

Jim

[tex628 · Accepted solution]

Of course! Normally what you would do is make sure that you have a calendar table attached to your fact table.

This will mean that any filter you apply to your calendar will affect your fact table. 

So you can try something along the lines of this:

MTD = 
VAR Year_ = YEAR(TODAY())
VAR Month_ = MONTH(TODAY()) 
Return
Calculate( [Sales] , ALL(Calendar) , Calendar[Year] = Year_ , Calendar[Month] = Month_ , Calendar[Date] <= TODAY())

This should always return the current MTD Sales Value. 

Br, 
J