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.
Thanks in advance,
In our report we have filters on the left and graphs on the right side. Once the filter is selected the graph needs to show the trend of the last 12 months from the date selected on the line graph. We tried follwing solution link. But we cannot get the expected output as the slicer filters the record as we apply the link .
We need override the slicer properties for this graph to work as expected. Kindly help to find the solution for it.
Solved! Go to Solution.
Hi @dylanjames1947,
Please refer to the following steps.
1. Create a calculated table.
Table = CROSSJOIN(VALUES('Summary Data'[Month]),VALUES('Summary Data'[Year]))
2. Create a calcualted column in the Summary Data table.
date = DATE('Summary Data'[Year],'Summary Data'[Month],1)
3. Create a measure to achieve our goal.
Measure 4 = VAR datesele = DATE ( SELECTEDVALUE ( 'Table'[Year] ), SELECTEDVALUE ( 'Table'[Month] ), 1 ) VAR seleyear = SELECTEDVALUE ( 'Summary Data'[Year] ) VAR selemon = SELECTEDVALUE ( 'Summary Data'[Month] ) VAR last6month1 = DATE ( SELECTEDVALUE ( 'Table'[Year] ), SELECTEDVALUE ( 'Table'[Month] ) - 6, 1 ) VAR last6month2 = DATE ( SELECTEDVALUE ( 'Table'[Year] ) - 1, 12 - ( 6 - SELECTEDVALUE ( 'Table'[Month] ) ), 1 ) RETURN IF ( selemon >= 7, IF ( MAX ( 'Summary Data'[date] ) <= datesele && MAX ( 'Summary Data'[date] ) >= last6month1, SUM ( 'Summary Data'[CurrentCount] ), BLANK () ), IF ( MAX ( 'Summary Data'[date] ) <= datesele && MAX ( 'Summary Data'[date] ) >= last6month2, SUM ( 'Summary Data'[CurrentCount] ), BLANK () ) )
4. For the table visual, we can put the measure into it and make the measure is not blank.
Also please find the pbix as attached.
Regards,
Frank
Check this one out:
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
We have already implemented trend analysis based on the date values, the problem arises when performing additional filteration that is done on the same data table, filters like department, organization unit, company, etc. When we apply these filters the graph shows only one month record.
It would be great if you could help with this problem.
Hi @dylanjames1947,
Kindly share your sample data and excepted result to me.
Regards,
Frank
Here is the link to the sample .pbix file, where we are showing trend for the past 6 months. On selecting a month, we need to show the trend for the past 6 months with the selected filter, but also show the selected details of that date.
Let me know if you require more information. Thanks in advance
Hi @dylanjames1947,
Please refer to the following steps.
1. Create a calculated table.
Table = CROSSJOIN(VALUES('Summary Data'[Month]),VALUES('Summary Data'[Year]))
2. Create a calcualted column in the Summary Data table.
date = DATE('Summary Data'[Year],'Summary Data'[Month],1)
3. Create a measure to achieve our goal.
Measure 4 = VAR datesele = DATE ( SELECTEDVALUE ( 'Table'[Year] ), SELECTEDVALUE ( 'Table'[Month] ), 1 ) VAR seleyear = SELECTEDVALUE ( 'Summary Data'[Year] ) VAR selemon = SELECTEDVALUE ( 'Summary Data'[Month] ) VAR last6month1 = DATE ( SELECTEDVALUE ( 'Table'[Year] ), SELECTEDVALUE ( 'Table'[Month] ) - 6, 1 ) VAR last6month2 = DATE ( SELECTEDVALUE ( 'Table'[Year] ) - 1, 12 - ( 6 - SELECTEDVALUE ( 'Table'[Month] ) ), 1 ) RETURN IF ( selemon >= 7, IF ( MAX ( 'Summary Data'[date] ) <= datesele && MAX ( 'Summary Data'[date] ) >= last6month1, SUM ( 'Summary Data'[CurrentCount] ), BLANK () ), IF ( MAX ( 'Summary Data'[date] ) <= datesele && MAX ( 'Summary Data'[date] ) >= last6month2, SUM ( 'Summary Data'[CurrentCount] ), BLANK () ) )
4. For the table visual, we can put the measure into it and make the measure is not blank.
Also please find the pbix as attached.
Regards,
Frank
Thanks so much Frank for the detailed solution, it's really really helpful for me.
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 |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
129 | |
106 | |
106 | |
86 | |
72 |