Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
delsbg
Helper I
Helper I

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
v-yingjl
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
v-yingjl
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.

amitchandak
Super User
Super User

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

ToddChitt
Super User
Super User

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.