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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Booyah
Frequent Visitor

Hide incomplete quarters in line chart

I've been struggling with this for hours and cannot seem to figure it out.


My issue is pretty straightforward. I have a line chart that shows data over the previous 4 years. One of the tables used is a financial table that loads data a month or two months behind. As of today, the data is loaded through November 2021. I need to hide 2021 - Q4 from the line chart until December data is loaded in.

 

It needs to be dynamic where I can do it and leave it - without having to come back every month or so to change something.

2 ACCEPTED SOLUTIONS
Whitewater100
Solution Sage
Solution Sage

Hi:

If you can add to your date table an index number for each Quarter. E.G., 

2018 Q 1 = 201801  0r  1

2018Q2 =201802     Or 2

.... 2021Q4 = 202104   or  16

This column is named index in this example

Then add calc cloumn:

Completed Qtr  =

      VAR ThisQtrID =

              LOOKUPVALUE(Dates[Index], Dates[Date], TODAY())

                   RETURN Dates[Index] < ThisQtrID

          //returns TRUE() if the quarter is in the past and complete

            set filter appropriately - set to TRUE //

 

There are other ways of doing it like having a date calendar with offset so 0 is the current quarter, so you filter not to include it. There are date tables available on the internet. If you need more help let me know.

 

Thanks..

View solution in original post

@Booyah It is funny you asked this, I was about to do a video on this in the next few days, maybe I will prioritize it. Subscribe to my youtube channel to stay updated (link below), if you found a solution before that, great.

 

 

Follow us on LinkedIn and YouTube.gif to our YouTube channel

 

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

 

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

View solution in original post

9 REPLIES 9
Booyah
Frequent Visitor

I've been struggling with this for hours and cannot seem to figure it out.


My issue is pretty straightforward. I have a line chart that shows data over the previous 4 years. One of the tables used is a financial table that loads data a month or two months behind. As of today, the data is loaded through November 2021. I need to hide 2021 - Q4 from the line chart until December data is loaded in.

 

It needs to be dynamic where I can do it and leave it - without having to come back every month or so to change something.

Booyah
Frequent Visitor

I've been struggling with this for hours and cannot seem to figure it out.


My issue is pretty straightforward. I have a line chart that shows data over the previous 4 years. One of the tables used is a financial table that loads data a month or two months behind. As of today, the data is loaded through November 2021. I need to hide 2021 - Q4 from the line chart until December data is loaded in.

 

It needs to be dynamic where I can do it and leave it - without having to come back every month or so to change something.

How do you know that/when a quarter is complete?

Awesome. Thanks for subscribing. Almost there 🙂



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

@Booyah It is funny you asked this, I was about to do a video on this in the next few days, maybe I will prioritize it. Subscribe to my youtube channel to stay updated (link below), if you found a solution before that, great.

 

 

Follow us on LinkedIn and YouTube.gif to our YouTube channel

 

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

 

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

That would be awesome. Just subscribed to the channel and I look forward to seeing that video!

Whitewater100
Solution Sage
Solution Sage

Hi:

If you can add to your date table an index number for each Quarter. E.G., 

2018 Q 1 = 201801  0r  1

2018Q2 =201802     Or 2

.... 2021Q4 = 202104   or  16

This column is named index in this example

Then add calc cloumn:

Completed Qtr  =

      VAR ThisQtrID =

              LOOKUPVALUE(Dates[Index], Dates[Date], TODAY())

                   RETURN Dates[Index] < ThisQtrID

          //returns TRUE() if the quarter is in the past and complete

            set filter appropriately - set to TRUE //

 

There are other ways of doing it like having a date calendar with offset so 0 is the current quarter, so you filter not to include it. There are date tables available on the internet. If you need more help let me know.

 

Thanks..

Hi @Booyah 

 

Can you post sample data as text and expected output?
Not enough information to go on;

please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
4. Relation between your tables

Appreciate your Kudos!!
LinkedIn:www.linkedin.com/in/vahid-dm/

Here is a super brief mock-up of what I want to see as the output. I am showing the total sales across several years 2018-present by each quarter. What I want to be able to do is create a calculated column as 'Complete Quarter' to set months in a quarter to "FALSE" if the quarter is not full.

CustomerIDDate of Sale StateTotal SalesComplete Quarter
1November 1, 2021NY$10FALSE
2October 1, 2021NY$20FALSE
3September 1, 2021CA$3TRUE
4August 1, 2021WI$5TRUE
5July 1, 2021TN$7TRUE


This table is loaded once a month. When it's loaded in, it's always a month behind. So the December 2021 data will not be loaded in until the next week or so.

If I have a calculated column like this, then I can set it to filter that line chart to only show "TRUE" values which will solve my issue of showing an incomplete quarter.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors