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

[dobregon]

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?

Thanks in advance and regards!

[Anonymous]

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]

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

[Anonymous · Accepted solution]

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.

[mac_oc]

@Anonymous 

nice solution, what if instead to display a measure a I need to display/hide a existing column in a table ( ex EUR columns vs$ columns) ? 

Thank you in advance,

mac

[dobregon]

mmmm i dont know the feature to do visibility (hide or not hide) , the only thing is to put or not the measure. So... based on an expresion... i dont know

the only thing that i could remember is if you use bookmarks, you can create the table with the column and other table without the column, and the action when you filter os to move to the bookmarks.. but maybe it is a little bit... hard.

[VisheshVats]

@Anonymous Thanks for your help. Could i ask something of an extension of a similar situation?

Plz have a look at my situation below.

As you can see I have managed to create a measure that can slice the visual based on the selection i make on the Select Index slicer.

Firstly, as you can see from my measure, the alternate result is Blank(), what can i do so that if nothing is selected, all the indexes are selected and the graph shows all the lines together.

Secondly, What i seek to achive further is to create a measure that will slice the x-axis(date) based on the selected value in the select period slicer. Like it should show date range only for 1 month back from today if i select 1 Month and so on. 

Thanks in advance

[carlosmeep]

Thanks for the reply @amitchandak :

That's what I want to do, but power bi doesn´t accept it, It won't let me put the column (the highlighted columns in the image)

For power bi that construction is wrong:

this is what I get:

doesn't recognice the column name

[Anonymous]

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]

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]

That worked. Thanks so much.

Now to test it some more

[dobregon]

You are welcome! @Anonymous

[dobregon]

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

[dobregon]

Hi @Anonymous

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.

Thanks in advance, regards!!!

[dobregon]

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"

[bhurru]

@dobregon This is exactly I'm looking for in my report. I have the same scenerio of changing Axis (Week,Month,Quarter) and restricting according to Time period selection.

However I do not get you when you say Created measure Filter for Yes/No when date is in the Time Period Range. Can you share some more details how you did it ?

regards,

Har**bleep**

[dobregon]

Hi @bhurru  2 years after is difficult to remember, but i will try.

1. first you need to have the measure switch that you can see in the post, in order to have the correct values when you select YTD;QTD;MTD in the slicer.

2. Filter timeseries: Maybe you have a chart with days or timestamp and if you select QTD you want to filter the days properly. To do that, you can create 3 little measures (YTD,QTD,MTD days) that calcualte YES/NOT if the day is YTD;QTD;MTD so you will have 3:

- YTD days: Will put yes in the days YTD
- QTD Days: will put yes only in the QTD days and NOT in rest

- MTD Days: bla bla bla

Then you need to create another measure swicht that when you select QTD take the QTD days (for example). Put this measure as a visual filter in your chart visual and prefilter by YES. with this when you click on QTD in the slicer you will see only the dates of QTD, etc etc etc

[bhurru]

@dobregon Thanks for the Try.

However I didn't get point  and 3. I'm done with Point 1 creating calc measures.

Can you please once explain these points once again ? Below is what I had done

I have created 4 columns in my calender tables IsRolling8Weeks,IsRolling26Weeks, IsRolling2Quarter etc... with values Yes or No based on Relative Weeks number and Quarter number I have calculated. 

Eg. Rolling8Weeks : where Relative Week between 0 and -7 and so on for others ... (Relative Week is 0 for Max week and keep decreasing for previous weeks till start date of Calender)

Also I have to apply this slicer change in all visuals and all pages.

[dobregon]

@bhurru  1. You need to create all the measures for your calculations of time (i imagen one for each column that you have created). the measures filtering the "yes" 2. Create a table with the "options" of time intelligence that you want to filter. i imagen that it should be the same 4 options that you have created in columns, but only one column with the 4 options as rows 3. put this as a filter slicer or in the filter pane 4. Create the general measure "hasonfilter" that depends of the options that you select in the point 3 you select the measure.

[dothaohieu]

Hi Sir, your post helped me a lot. It must be hard for you to remember after 2 years. I have a question and I'm finding the answer, but not yet.

I created a filter like the way you said. My filter included: today, yesterday, last 7 days, last 30 days, current month, last month, current year, current quarter. That's all the filter I needed. But I also had a measure called "LYTD-sales" to calculate the same period last year. Here is my DAX: CALCULATE (SUM(Sales[total_sales]),DATEADD('Date'[Date],-1,YEAR))

What I want is when I choose one of the filters, "LYTD-sales" also return exactly the result for every period of time that I created. Do you have any ideas or solutions to this problem? Thank you in advance.

[Dang_Bach]

Welp there is Fields Parameter now