cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dobregon Member
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:
Filter_sourcedata.PNG

 

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)Filter_result1.PNG

 

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:Filter_result2.PNG

 

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?

Thanks in advance and regards!

1 ACCEPTED SOLUTION

Accepted Solutions
SPG Member
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.

View solution in original post

10 REPLIES 10
SPG Member
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()
)
dobregon Member
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.calendarfilter.png

 

 

 

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

 

SPG Member
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.

View solution in original post

dobregon Member
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!

 
dobregon Member
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)

    timeframes.png

     

    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".
    timeframes_dates.png
    - 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.

     

Thanks in advance, regards!!!

 

dobregon Member
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.

dobregon Member
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

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)