Implementing Year-To-Date, Month-To-Date, and Today via ChicletSlicer

[Anonymous]

I have a table that has Sales data. The columns are GrossSalesAmount and Date. I want to use the ChicletSlicer to categorize the data into 3 date filters: Year-To-Date (YTD), Month-To-Date (MTD), and Today (TOD). what I did so far is the following: I created a calculated column that gives each row a value: Y (for Year), M (for Month), and T (for Today). If the value of Date is for today, it will give the value T. Else, if the value of the date is the current month, it will give the value M. Else, if the value of the date is the current year, it will give the value Y. Else, it will give the row the value N (for None). The table will look something like this (for example's sake, assume today is February 13th 2019): GrossSalesAmount | Date | Filter ------------------------------------------------------ 12,333 | 13 Feb 2019 | T ------------------------------------------------------ 444,444 | 10 Feb 2019 | M ------------------------------------------------------ 444,444 | 4 Feb 2019 | M ------------------------------------------------------ 111,111 | 24 Jan 2019 | Y ------------------------------------------------------ 111,111 | 15 Jan 2019 | Y Now, I created another table called Filter which has One colmn, Category, with 3 values: Y, M, and T. I created a one-to-many Single relationship between Category (from Filter table) and Filter (from Sales table). The ChicletSlicer uses the column Category to list the 3 filters Y, M, and T. What I want is the following: when the user clicks on T, it will show him the Sales data of the row that has the value T. If the user clicks on M, it will show him the Sales data of the rows labeled BOTH T and M. If the user clicks on Y, it will show him the Sales data of the rows labeled T AND M AND Y. How do I do this?

[Anonymous]

I'm still struggling with this issue. Can someone help me?

[v-juanli-msft]

Hi @Anonymous

in my test, today is 2019/1/15

Create measures in Table1

selected = SELECTEDVALUE(Table2[cate])

value_1 =
SWITCH (
    [selected],
    "Y", YEAR ( MAX ( Table1[Date] ) ),
    "M", MONTH ( MAX ( Table1[Date] ) ),
    "T", DAY ( MAX ( Table1[Date] ) )
)

value_2 =
SWITCH (
    [selected],
    "Y", YEAR ( TODAY () ),
    "M", MONTH ( TODAY () ),
    "T", DAY ( TODAY () )
)

flag = IF([value_1]=[value_2],1,0)

don't create any relationship between Table 1and Table2,

add [flag] in the visual level filter

“

Community Support Team _ Maggie Li

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

“

Best Regards

maggie

[Anonymous]

Thanks, but this only works if I'm displaying the data in a table or a matrix. I need to display the data in a clustered bar chart.

[v-juanli-msft]

Hi @Anonymous

Is this what you want?

Best Regrads

Maggie

[Anonymous]

Hello @v-juanli-msft 

I want to display the GrossSalesAmount by Branch. I forgot to mention that there is a Branch column in the Sales table. Now the bar chart has the Axis as Branch, and the Value is GrossSalesAmount. I want to use the ChicletSlicer as a DateCategory filter. Meaning that I select YTD, MTD, or Today and the values of the bar chart change based on my selection.