YTD, QTD, MTD slicers

[nleuck]

Hello Everyone,

I have a report that I'm creating that summarizes our company's training being completed. I have two tables one with the training data and the other is a date table. In the training table I have two dates Date Assigned and Date Completed. I've created two relationships to the date table but neither one is active. I've created three columns in my date table:

IsYTD = IF(YEAR(DateTable[Date]) = YEAR(TODAY()), 1, 0)

IsQTD = IF(QUARTER(DateTable[Date]) = QUARTER(TODAY()), 1, 0)

IsMTD = IF(MONTH(DateTable[Date]) = MONTH(TODAY()), 1, 0)

I want to be able to create slicers or just one slicer to filter my data, but I'm not sure it's possible because I have two dates in my training table. I really don't want to have to create YTD, QTD, MTD measures for both Date Assigned and Date Completed.

Any ideas?

Thanks,

[v-robertq-msft]

Hi, @nleuck 

According to your description, I can roughly understand what you want to get. You want to just create three Slicers(YTD, QTD, MTD) to slice the data. But I find it hard to create the test data to meet your requirement in detail. Would you like to post some sample data in table form or pbix file(without sensitive data) and your expected result(like the chart you want to get and the correct measure value based on your sample data)?

Thanks very much!

How to Get Your Question Answered Quickly 

Best Regards,

Community Support Team _Robert Qin

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

[nleuck · Accepted solution]

Hello Everyone,

I was able to come up with a solution that worked for me. I created two different meausres:

Total Classes Assigned - CALCULATE(COUNT(TrainingData[DateAssigned]),

                                             USERELATIONSHIP(TrainingData[DateAssigned], DateTable[Date]))

Total Classes Completed - CALCULATE(COUNTTrainingData[DateCompleted]),

                                              USERELATIONSHIP(TrainingData[DateCompleted, DateTable[Date]))

In my Date table I had three columns that I created IsYTD, IsQTD, IsMTD ( I made adjustments to IsQTD and IsMTD):

IsYTD = IF(DateTable[Date] = YEAR(TODAY()), 1, 0)

IsQTD = IF(QUARTER(DateTable[Date]) = QUARTER(TODAY()) && YEAR(DateTable[Date]) = YEAR(TODAY()), 1, 0)
IsMTD = IF(MONTH(DateTable[Date]) = MONTH(TODAY()) && YEAR(DateTable[Date] = YEAR(TODAY()), 1, 0)

I then used those columns to create my own slicer visual:

Thank you for those that tried to help!

[amitchandak]

@nleuck , What kind of slicer you need is not clear?

While time intelligence can help  you. Make sure you userelationship  activate you relationship

example

MTD Sales = CALCULATE(CALCULATE(SUM(Date[Amount]), userealtionship('Date'[Date], Data[Date Assigned]) ),DATESMTD('Date'[Date]))

Same will true for QTD and YTD

examples

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))
next month Sales = CALCULATE(SUM(Sales[Sales Amount]),nextmonth('Date'[Date]))
this month =MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH('Date'[Date])))
last MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
previous month value =  CALCULATE(sum('Table'[total hours value]),previousmonth('Date'[Date]))



QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(('Date'[Date])))
Last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,QUARTER)))

Last QUARTER Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD( ENDOFQUARTER(dateadd('Date'[Date],-1,QUARTER))))
Last QUARTER Sales = CALCULATE(SUM(Sales[Sales Amount]),PREVIOUSQUARTER(('Date'[Date])))

Last to last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-2,QUARTER)))

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))

TO get meausre slicer refer these

measure slicer
https://radacad.com/change-the-column-or-measure-value-in-a-power-bi-visual-by-selection-of-the-slic...
https://community.powerbi.com/t5/Desktop/Slicer-MTD-QTD-YTD-to-filter-dates-using-the-slicer/td-p/50...

[nleuck]

@amitchandak 

My issue is I don't want to create multiple measures for Total Classes Assigned and Total Classes Completed. I would have to create three for each YTD, QTD, and MTD. I would like to have either one slicer that filters YTD, QTD, and MTD or three separate slicers for YTD, QTD, and MTD. It would be nice if the user could select YTD, QTD, or MTD and the data filters for both Total Classes Assigned and Total Classes Completed.

For example, a class could be assigned to a user in one month but completed in other. If I make the relationship active for Date Assigned, my Total Classes Completed will not be correct. It will only show Total Classes Completed that were assigned in that month. This is not an accurate total because someone can complete a training course in a different month than it's assigned.