Date comparison in a measure with aggregation

[jimbob2285]

Hi everyone

I'm trying to adapt your solution that I've seen on here for a chiclet slicer with select all - using the same technique to enable a period chiclet slicer with Week-to-date (WTD), Month-to-date (MTD) and Year-to-date (YTD), obviously YTD includes the records from MTD and MTD includes the records from WTD, so it's not a simple column in the table, so I've got the below below, using a slicer values table and then calling the selected value from my sales table and validating sales dates based on that selected value to privide a column with "Show" or "No" that i can filter my visuals on and they'll change as the chiclet slicer changes

_PeriodSelected =
SWITCH (True(),
SELECTEDVALUE(SlicerValues[Period]) = "Year-To-Date", "Show",
SELECTEDVALUE(SlicerValues[Period]) = "Month-To-Date" && MONTH(SalesOrders[Date]) = MONTH(Today()), "Show",
SELECTEDVALUE(SlicerValues[Period]) = "Week-To-Date" && WEEKNUM(SalesOrders[Date]) = WEEKNUM(Today()), "Show",
"No")

The above works in theory btu won't work as a

• Measure because the SalesOrder[Date] isn't aggregated and aggregating it with sun, min, ect doesn't give the desired result
• Calculated Column because you can only use SELECTEDVALUE in a Measure

So I've hit a wall, I need to do this as a calculated column to perform the date checks without aggregating the dates, but you can only use SELECTEDVALUE in a measure, which means I need to aggregate the dates, which doesn't...

I've tried using SELECTEDVALUE in a measure and then referencing the measure in a column, but it just won't pull the measure through to the colunm and reading a bit deeper I can see why, so I've abandoned those efforts and switched to trying to do the date comparisons in the measure, but again you need to aggregate in a measure, so the date comparisons don't work

I'm stuck, any ideas?

Cheers

Jim

[v-stephen-msft]

Hi @jimbob2285 ,

First, you need the YTD, MTD and WTD measures. Then just create the following

Final Value = 
IF(HASONEFILTER(SlicerValues[Period]),
    SWITCH(SELECTEDVALUE(SlicerValues[Period]),
        "YTD", [Measure YTD],
        "MTD", [Measure MTD],
        "WTD", [Measure WTD]
    ),
   BLANK()
)

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

"No, I'm not saying that weeks are confined to months... but MTD includes WTD " 

Which one is it?

Is your data source in import mode?  if yes, how often do you refresh it?  If you do a daily refresh then you can do the aggregation as part of the data load.

[jimbob2285]

Hi

So lets forget about Weeks and WTD for the time being and focus on MTD and YTD

Results for a YTD filter also need include MTD results... MTD sits within the YTD period (Aug 2021 MTD is part

 of 2021 YTD, so i can't simply have a column that flags Aug as MTD and the rest of the sales data for 2021 as YTD, because YTD as a filter would then exclude Aug MTD

The select all solution I'm trying to adapt seemed perfect, but when i try to implement it i get an error becuase the sales table contains mutiple entries for the same date:

There'll be a record for each salesteam for a date, and i can;t eggregate them with sum or min, so the month comparison between sales date and today() needs to be in a calculated column, because it needs to calculated on each record, but you can only use SELECTEDVALUE in a measure... I've also tried adding a Calendar table linked to the SalesTable[Date], thinking that would only have a single record for each data, but i get the same error

Sorry if I;ve not been clear before, does that clarify what i mean - I really don;t know where to go from here, i can't be the first person to want to achieve this, i just wont the user to be able to click YTD, MTD or WTD and get the correct sales results in the tabel visual

Thanks

JIm

[jimbob2285]

No, I'm snot saying that weeks are confined to months

I want it to be a chiclet slicer, so the user just selects YTD or MTD or WTD, but MTD includes WTD and YTD includes MTD, so it can't be a field filter or MTD would exclude WTD, which wouldn't work

So I'm trying to adapt a solution I've found in this group where the the slicer options are in a seperate table and a measure in my sales table looks up the selected item in the slicer table and then compares sales date with current date, e.g. where MTD is selected it looks for sales who's month are the same as the current month

however, SELECTEDVALUE only seems to work in a measure and i can't do the date comparison in a measure, the date comparison needs to be performed in a calculated column because a meausres requires aggregation.... how do you aggregate a date and then compare it with today()?

[lbendlin]

Are you saying your weeks are confined to month boundaries? What kind of calendar is that?

Does it really have to be a hierarchical slicer? Would it work with three regular slicers?  Or no slicers at all, but instead fields in the filter pane?