WTD MTD YTD Slicer with Button

[mpw]

Hello! I am using the below formula to try to work with a slicer I created for WTD, MTD, YTD.

When I add the slicer to the page and the measurement, it doesn't seem to work properly with my data.

EL Month Conversion is the date table I am using based on my company's fiscal calendar.

NA Sales w LY is the table with all the sales data.

I would also like to include QTD.

Looking for a solution where the data changes with a click of a button like below.

WTD =

var _select=SELECTEDVALUE('EL Month Conversion'[Retailer Week])

var _select1=SELECTEDVALUE('Time Slicer'[Measure Name])

var _mtd=CALCULATE(SUM('NA Sales w LY'[TY]),FILTER(ALL('EL Month Conversion'),'EL Month Conversion'[Retailer Week]>=DATE(YEAR(_select),MONTH(_select),1)&&'EL Month Conversion'[Retailer Week]<=_select))

var _wtd=CALCULATE(SUM('NA Sales w LY'[TY]),FILTER(ALL('EL Month Conversion'),YEAR(MAX('EL Month Conversion'[Retailer Week]))= YEAR(_select)&&WEEKNUM('EL Month Conversion'[Retailer Week],1)=WEEKNUM(_select,1)))

var _ytd=CALCULATE(SUM('NA Sales w LY'[TY]),FILTER(ALL('EL Month Conversion'),'EL Month Conversion'[Retailer Week]>=DATE(YEAR(TODAY()),1,1)&&'EL Month Conversion'[Retailer Week]<=_select))

return

SWITCH(

    TRUE(),

 _select1="YTD",_ytd,

_select1="MTD",_mtd,  

    _select1="WTD",_wtd)

Thank you!

[v-tangjie-msft]

Hi @mpw ,

According to your description, here are my steps you can follow as a solution.

(1) This is my test data. 

(2) We can create a measure. 

WTD MTD YTD QTD = 
VAR _select = SELECTEDVALUE('EL Month Conversion'[Retailer Week])
VAR _select1 = SELECTEDVALUE('Time Slicer'[Measure Name])
VAR _mtd = CALCULATE(SUM('NA Sales w LY'[TY]), FILTER(ALL('EL Month Conversion'), 'EL Month Conversion'[Retailer Week] >= DATE(YEAR(_select), MONTH(_select), 1) && 'EL Month Conversion'[Retailer Week] <= _select))
VAR _wtd = CALCULATE(SUM('NA Sales w LY'[TY]), FILTER(ALL('EL Month Conversion'), YEAR(MAX('EL Month Conversion'[Retailer Week])) = YEAR(_select) && WEEKNUM('EL Month Conversion'[Retailer Week], 1) = WEEKNUM(_select, 1)))
VAR _ytd = CALCULATE(SUM('NA Sales w LY'[TY]), FILTER(ALL('EL Month Conversion'), 'EL Month Conversion'[Retailer Week] >= DATE(YEAR(TODAY()), 1, 1) && 'EL Month Conversion'[Retailer Week] <= _select))
VAR _qtd = CALCULATE(SUM('NA Sales w LY'[TY]), FILTER(ALL('EL Month Conversion'), 'EL Month Conversion'[Retailer Week] >= STARTOFQUARTER('EL Month Conversion'[Retailer Week]) && 'EL Month Conversion'[Retailer Week] <= _select))
RETURN
SWITCH(
    TRUE(),
    _select1 = "YTD", _ytd,
    _select1 = "MTD", _mtd,
    _select1 = "WTD", _wtd,
    _select1 = "QTD", _qtd
)

(3) Then the result is as follows.

For more details, refer: STARTOFQUARTER – DAX Guide

Best Regards,

Neeko Tang

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

[mpw]

Hi @v-tangjie-msft thank you for your reply! I copied the measure above but the values don't change when I click on MTD, QTD, etc. from the Time Slicer table. Is there another step I need to do for this to work?

[v-tangjie-msft]

Hi @mpw ,

You can create a slicer table. Please note that the slicer table has no model relationship with other tables.

Time Slicer = DATATABLE ( 
    "Measure Name", STRING, 
    {
        { "YTD"},
        { "MTD" },
        { "WTD" },
        { "QTD" }
    }
) 

Then the result is as follows.

Best Regards,

Neeko Tang

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

[mpw]

i followed step by step and this did not work for me. the data is still not changing.