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