Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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,
Solved! Go to 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!
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:
Thank you for those that tried to help!
@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...
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
96 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |