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.
We have two tiles in our customer complaint report. The previous quarter tile is blank unless there is some data for the current quarter (ie a complaint) but we don't get many complaints. I think it might be related to my date table and the context of the dates included in the report. Is there a workaround so that Power BI is always aware of the current date and bases it's measures off of it regardless of whether there are any data points from the current period/quarter?
Solved! Go to Solution.
Hi @delsbg ,
Based on your description, you can create this measure and put it in the bar chart:
Previous =
IF (
CALCULATE (
SUM ( 'Table'[Value] ),
FILTER (
ALL ( 'Table' ),
YEAR ( 'Table'[date] ) = YEAR ( TODAY () )
&& QUARTER ( 'Table'[date] ) = QUARTER ( TODAY () )
)
) = 0,
IF (
(
YEAR ( TODAY () )
= SELECTEDVALUE ( 'Table'[date].[Year] ) + 1
&& SELECTEDVALUE ( 'Table'[date].[QuarterNo] ) = 4
)
|| (
YEAR ( TODAY () ) = SELECTEDVALUE ( 'Table'[date].[Year] )
&& QUARTER ( TODAY () )
= SELECTEDVALUE ( 'Table'[date].[QuarterNo] ) + 1
),
0,
SUM ( 'Table'[Value] )
)
)
Attached a sample file in the below, hopes to help you.
Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @delsbg ,
Based on your description, you can create this measure and put it in the bar chart:
Previous =
IF (
CALCULATE (
SUM ( 'Table'[Value] ),
FILTER (
ALL ( 'Table' ),
YEAR ( 'Table'[date] ) = YEAR ( TODAY () )
&& QUARTER ( 'Table'[date] ) = QUARTER ( TODAY () )
)
) = 0,
IF (
(
YEAR ( TODAY () )
= SELECTEDVALUE ( 'Table'[date].[Year] ) + 1
&& SELECTEDVALUE ( 'Table'[date].[QuarterNo] ) = 4
)
|| (
YEAR ( TODAY () ) = SELECTEDVALUE ( 'Table'[date].[Year] )
&& QUARTER ( TODAY () )
= SELECTEDVALUE ( 'Table'[date].[QuarterNo] ) + 1
),
0,
SUM ( 'Table'[Value] )
)
)
Attached a sample file in the below, hopes to help you.
Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@delsbg , Are you using time intelligence and date table for that ?
example
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])))
Power BI — Qtr on Qtr with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-qtd-questions-time-intelligence-2-5-d842063da839
https://www.youtube.com/watch?v=8-TlVx7P0A0
Check Why Time Intellignce Fails : https://www.youtube.com/watch?v=OBf0rjpp5Hw
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.
A typical solution I do for YTD, MTD, or QTD calculations/metrics is to add the tile to a report page, then also add a Date slicer, set it to Relative, and select This Day. If the data is based on a Date dimension, it will always show the current Date. So the MTD or YTD visuals on the same Page always 'know' what the current day is. When you Pin the visual to a Dashboard, that slicer/logic should go with it.
Proud to be a Super User! | |
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 | |
97 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |