cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

PreviousQuarter measure/tile blank if no data yet in current quarter

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?

1 ACCEPTED SOLUTION
Community Support
Community Support

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] )
    )
)

pre.png

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.

View solution in original post

3 REPLIES 3
Community Support
Community Support

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] )
    )
)

pre.png

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.

View solution in original post

Super User IV
Super User IV

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



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!

Post Prodigy
Post Prodigy

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.

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

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.

Top Solution Authors
Top Kudoed Authors