Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
I am trying to create a dynamic line on my line chart to indicate today's date. and also make future data a bit transparent. Any idea how to achieve this? I saw a few solutions about creating a new measure and use the line and stacked chart. it doesn't apply to my use case because I don't have data points for all date. This is the file:
https://drive.google.com/drive/folders/1aZwMygofzomDdI9Tsa9W2qxzhi1dRKfD?usp=sharing
Here is what I am tyring to achieve:
Dataset looks like this:
Period | Group | Profits |
2020-08-15 0:00 | Group B | 526650.0483 |
2020-08-15 0:00 | Group A | 358686.7175 |
2020-08-31 0:00 | Group B | 479743.9389 |
2020-08-31 0:00 | Group A | 184101.7028 |
2020-09-15 0:00 | Group B | 381205.785 |
2020-09-15 0:00 | Group A | 308477.628 |
2020-09-30 0:00 | Group B | 307498.7097 |
2020-09-30 0:00 | Group A | 266755.2061 |
2020-10-15 0:00 | Group B | 473268.3453 |
2020-10-15 0:00 | Group A | 326478.0087 |
2020-10-31 0:00 | Group B | 325795.688 |
2020-10-31 0:00 | Group A | 162072.1676 |
2020-11-15 0:00 | Group B | 818205.48 |
2020-11-15 0:00 | Group A | 369871.0837 |
2020-11-30 0:00 | Group B | 490788.7987 |
2020-11-30 0:00 | Group A | 422384.8563 |
2020-12-15 0:00 | Group B | 494155.8499 |
2020-12-15 0:00 | Group A | 202818.2976 |
2020-12-31 0:00 | Group B | 216382.5483 |
2020-12-31 0:00 | Group A | 84604.37819 |
2021-01-15 0:00 | Group B | 328950.9571 |
2021-01-15 0:00 | Group A | 278617.7077 |
2021-01-31 0:00 | Group B | 428521.5152 |
2021-01-31 0:00 | Group A | 144391.1824 |
2021-02-15 0:00 | Group B | 326873.0559 |
2021-02-15 0:00 | Group A | 289905.5259 |
2021-02-28 0:00 | Group B | 294048.3064 |
2021-02-28 0:00 | Group A | 104598.8772 |
2021-03-15 0:00 | Group B | 335980.1284 |
2021-03-15 0:00 | Group A | 251847.6486 |
2021-03-31 0:00 | Group B | 245569.5233 |
2021-03-31 0:00 | Group A | 134085.6492 |
Solved! Go to Solution.
Hi @windynan ,
I didn't find any chart meets your requirements. You can vote for conditional formatting for line chart : https://ideas.powerbi.com/ideas/idea/?ideaid=0671c26d-bd0e-43ad-b78c-bb097f2c54af
As a workaround, you can do some overlapping data point between History and future like:
GroupAFuture = IF(MAX(Dim_Date[Date])>=TODAY()-3,CALCULATE(SUM(data[Profits]),data[Item] = "Group A"),BLANK())
GroupAHistory = IF(MAX(Dim_Date[Date])<=TODAY()+3,CALCULATE(SUM(data[Profits]),data[Item] = "Group A"),BLANK())
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
Hi @windynan ,
You can create a dim_date table and create one to many relationship to fact table:
Dim_Date = CALENDAR(MIN(data[Period]),MAX(data[Period]))
Then you can use the following three measure in your line and stacked column chart:
GroupA = CALCULATE(SUM(data[Profits]),data[Item] = "Group A")
Group B = CALCULATE(SUM(data[Profits]),data[Item] = "Group B")
TODAYLINE = IF(SELECTEDVALUE(Dim_Date[Date]) = TODAY(), 20000)
Please refer to the pbix file.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
Thanks @v-deddai1-msft ! Very helpful, but this is half of what I need. Based on this, I am still looking for ways to make future data points transparent, like what I showed in my picture. I tried to created four groups, like:
Group A History,
Group A future,
Group B History,
Group B future
However, there will be gaps when I put them on the line charts. Any idead how to link the charts? or any other ways that you can think about make future data lighter color?
Hi @windynan ,
I didn't find any chart meets your requirements. You can vote for conditional formatting for line chart : https://ideas.powerbi.com/ideas/idea/?ideaid=0671c26d-bd0e-43ad-b78c-bb097f2c54af
As a workaround, you can do some overlapping data point between History and future like:
GroupAFuture = IF(MAX(Dim_Date[Date])>=TODAY()-3,CALCULATE(SUM(data[Profits]),data[Item] = "Group A"),BLANK())
GroupAHistory = IF(MAX(Dim_Date[Date])<=TODAY()+3,CALCULATE(SUM(data[Profits]),data[Item] = "Group A"),BLANK())
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
Two potential suggestions:
1. Build it with the Charticulator visual, and add a measure that returns either 1 or 0.5 to be used as the Opacity value of the Marks/Links based on whether the date is past or future.
(2) Season 1 - Charticulator - YouTube
2. Use the native visual along with 4 measures, one for each combination of past/future and Group A/B, and set the desired colors for each.
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
86 | |
84 | |
83 | |
67 | |
49 |
User | Count |
---|---|
131 | |
111 | |
96 | |
71 | |
67 |