cancel
Showing results for
Did you mean:
Member

## Slicer MTD, QTD, YTD to filter dates using the slicer.

Hi all,

I have a question that i think it is not solved in the forum, as many of you i have the necessity to create some visuals/reports with the info in MTD, QTD and YTD. For that and now i'm doing new measures like CALCULATE(sum(sales),DATESYTD(date)) for every calculation and every MTD, QTD and YTD so, for every column i need to do 4 measures (simple actual without filter of dates, mtd, qtd and ytd) and it is not a big problem (only one time in every report) but i need to have one page for each MTD, QTD, YTD and i am asking if it is possible to create any type of slicer that i can filter for MTD, QTD, YTD...

This is an example of my source data:

And the table matrix that i want, something like that (this is an example in excel but the results in BI will be the same)

Now i'm creating one table matrix for each MTD, QTD and YTD with the calculated measures for each table, but i'm looking if it is possible to do something like that:

So, the MTD, QTD and YTD should be an slicer that when I click in MTD the report filters the dates of MTD and then the table shows the the values corresponding to that group of dates.

Could it be possible?

1 ACCEPTED SOLUTION

Accepted Solutions
Member

## Re: Slicer MTD, QTD, YTD to filter dates using the slicer.

It may be easier if you create 20 measures like the one I wrote yesterday:

```Final Value 1 =
IF(HASONEFILTER(Table[CalcType]),
SWITCH(SELECTEDVALUE(Table[CalcType]),
"MTD", [Measure 1 MTD],
"QTD", [Measure 1 QTD],
"YTD", [Measure 1 YTD]
),
BLANK()
)```

You already have the MTD, QTD and YTD measures, and the 20 you need to create are just copy and paste. Not much work.

10 REPLIES 10
Member

## Re: Slicer MTD, QTD, YTD to filter dates using the slicer.

Create an unrelated table with the text values "MTD", "QTD" and "YTD".

Add this column to a slicer (Chiclet Slicer would be better)

Then create a measure to select the right value depending on the selected value:

```Final Value =
IF(HASONEFILTER(Table[CalcType]),
SWITCH(SELECTEDVALUE(Table[CalcType]),
"MTD", [Measure MTD],
"QTD", [Measure QTD],
"YTD", [Measure YTD]
),
BLANK()
)```
Member

## Re: Slicer MTD, QTD, YTD to filter dates using the slicer.

Thanks for the response,

I can't use this becasue as Fuel, Maintenance (exmaples) i have more colmuns with values so it is not one only column with final value that i can filter using MTD, QTD, YTD formulas. in this case i have 20 columns with values so i have 20columns with actual values (no filters), 20 measures for MTD values, other 20measures for QTD values and other 20 measures for YTD values. So for that reason because there is not only one MEasuire MTD i have 20measures MTD.

I need to create a measure that calculate the days for MTD (using the max value from a slicer). I have tried to create a calculated table

CALENDAR =
VAR BASECALENDAR =
CALENDAR ( DATE ( 2018, 7, 1 ), table[Max date] )
RETURN
GENERATE (
BASECALENDAR,
VAR BASEDATE = [DATE]
VAR YEARDATE = YEAR ( BASEDATE )
VAR MONTHNUMBER = MONTH ( BASEDATE )
RETURN ROW (
"DAY", BASEDATE,
"YEAR", YEARDATE,
"MONTH NUMBER", MONTHNUMBER,
"MONTH", FORMAT ( BASEDATE, "MMMM" ),
"YEAR MONTH", FORMAT ( BASEDATE, "MMM YY" )
)
)

That i could use to create MTD, QTD and YTD creating 3 new columns but the problem in the creation of the calendar is the table[maxdate] in my table i generate every day a row of data for each transporter (bike, car, etc) so for example today i have the row values for yesterday 26th August but in the powerbi the people want to filter until the 23th (exmaple) and then automtically calculated the MTD, QTD and YTD using the enddate 23th August, and the problem in the creation of the calendar is that uses the end date of the calendar the max date source data no the max day filtered in the slider.

I dont know if i'am explaining well my idea, it is very crazy jajajaja

Member

## Re: Slicer MTD, QTD, YTD to filter dates using the slicer.

It may be easier if you create 20 measures like the one I wrote yesterday:

```Final Value 1 =
IF(HASONEFILTER(Table[CalcType]),
SWITCH(SELECTEDVALUE(Table[CalcType]),
"MTD", [Measure 1 MTD],
"QTD", [Measure 1 QTD],
"YTD", [Measure 1 YTD]
),
BLANK()
)```

You already have the MTD, QTD and YTD measures, and the 20 you need to create are just copy and paste. Not much work.

Member

## Re: Slicer MTD, QTD, YTD to filter dates using the slicer.

oh thanks, I didn't think about that but my problem is solved. Thanks a lot!

Member

## Re: Slicer MTD, QTD, YTD to filter dates using the slicer.

Hi @SPG

I have done the 20 new measurements with this option and it is was very useful to me but i have found a little problem that maybe you know how to resolve it.

as you know with the functions DAX to QTD values like... CALCULATE(sum(table[cost]),DATESQTD(table[dates])) an using your last reply about the functions to calculate MTD, QTD and YTD when i select it in the slicer..

The problem is when i take a different visual than a Table Matrix, in this case, a line chart with (imagine)

• Axis --> Table[dates] -- Dates from the 1st of Jan to Yesterday (information that i have always in my system)
• Values --> Sum(table[cost])

The problem is that in this visual the visual is taking into account all the dates that i have in the source, and i'm looking for a solution that this chart take into account only the dates from MTD, QTD or YTD. I've been thinking:

1. I have performed 2 measures using your formula that depends of the selection of MTD, QTD, YTD the system calculates de start day of the analysis and the end day of the analysis (example down)

So i have 2 measures that calculates de date start and date end of my calendar depends of the selection WTD, MTD, QTD and YTD and this is what i've been thinking.

- Change my query to my datasource using a directquery with these 2 parameters --> But it is not a good solution becasue it means that every time that someone change from mtd to qtd the system will do the query and it takes some time.

- Any possibility to filter the table2[date] axis in the visual in order to take dates after of equal than the Date Start TimeFrame but i dont found any posibility to filter using a "query" only selection in the calendar. But i dont know if it is possible becasue if it is possible in realilty in dont need to use 20measuremets with the functions. Only 2 (date start and date end) and apply the filter calendar in every "visual".

- It is possible to create a table every time that i change the filters (MTD, QTD, YTD) and creates the calendar only for this these 2 var (date start and date end)? Something like Createtable calendar from start date and end date? and then I can do a relationship with my source and use this "calendar" in the visuals.

Member

## Re: Slicer MTD, QTD, YTD to filter dates using the slicer.

solved, It is necesarry to create a measure "filter" with values (YES, NOT) that calculates if the date is between the period of startdate and end date of the interval.

Then insert in the filter visual and filter by "yes"

Anonymous
Not applicable

## Re: Slicer MTD, QTD, YTD to filter dates using the slicer.

This was very useful. However, I need on extra option. Users want to be able to select MTD, QTD, YTD, OR a "range of dates".

Hard to test if the MTD, QTD, YTD are working correctly, since we are in Month 1, QTR 1 of the current year.

How can a I add a "range" option that perhaps opens another slicer with range or allows start/end date entry.

Member

## Re: Slicer MTD, QTD, YTD to filter dates using the slicer.

Hi @Anonymous

I did that you are looking for. In the page you need to have 2 slicers: The slicer with the options WTD. MTD, YTD, etc and the other slicer with the dates (start date and end date) and when you create the maasures do something like this

```Actual Value TimeFrames =
IF(HASONEFILTER(TimeFrame[Interval]),
SWITCH(SELECTEDVALUE(TimeFrame[Interval]),
"1.WTD", Actual[Value 7D],
"2.MTD", Actual[Value  MTD],
"3.QTD", Actual[Value  QTD],
"4.YTD", Actual[Value YTD]
),
Actual[Value]
)```

So, if you click in any interval, the system will take the end date and take WTD, MTD, etc... but if you dont select any timeintertal the system will do the calculations for timerange that you select.

Anonymous
Not applicable

## Re: Slicer MTD, QTD, YTD to filter dates using the slicer.

That worked. Thanks so much.

Now to test it some more

Announcements