cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
nleuck
Post Patron
Post Patron

YTD, QTD, MTD slicers

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,

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

 

MTD_QTD_YTD slicer.png

 

Thank you for those that tried to help!

View solution in original post

4 REPLIES 4
v-robertq-msft
Community Support
Community Support

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.

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:

 

MTD_QTD_YTD slicer.png

 

Thank you for those that tried to help!

View solution in original post

amitchandak
Super User IV
Super User IV

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

 

 

 

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.