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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
windynan
Advocate I
Advocate I

Create dynamic line on x-axis to show today's date

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:

windynan_0-1625523505958.png

 

Dataset looks like this:

 

PeriodGroupProfits
2020-08-15 0:00Group B526650.0483
2020-08-15 0:00Group A358686.7175
2020-08-31 0:00Group B479743.9389
2020-08-31 0:00Group A184101.7028
2020-09-15 0:00Group B381205.785
2020-09-15 0:00Group A308477.628
2020-09-30 0:00Group B307498.7097
2020-09-30 0:00Group A266755.2061
2020-10-15 0:00Group B473268.3453
2020-10-15 0:00Group A326478.0087
2020-10-31 0:00Group B325795.688
2020-10-31 0:00Group A162072.1676
2020-11-15 0:00Group B818205.48
2020-11-15 0:00Group A369871.0837
2020-11-30 0:00Group B490788.7987
2020-11-30 0:00Group A422384.8563
2020-12-15 0:00Group B494155.8499
2020-12-15 0:00Group A202818.2976
2020-12-31 0:00Group B216382.5483
2020-12-31 0:00Group A84604.37819
2021-01-15 0:00Group B328950.9571
2021-01-15 0:00Group A278617.7077
2021-01-31 0:00Group B428521.5152
2021-01-31 0:00Group A144391.1824
2021-02-15 0:00Group B326873.0559
2021-02-15 0:00Group A289905.5259
2021-02-28 0:00Group B294048.3064
2021-02-28 0:00Group A104598.8772
2021-03-15 0:00Group B335980.1284
2021-03-15 0:00Group A251847.6486
2021-03-31 0:00Group B245569.5233
2021-03-31 0:00Group A134085.6492

 

1 ACCEPTED 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())

 

Capture65.PNG

 

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

View solution in original post

4 REPLIES 4
v-deddai1-msft
Community Support
Community Support

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)

 

Capture64.PNG

 

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

 

Capture65.PNG

 

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

mahoneypat
Microsoft Employee
Microsoft Employee

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

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Helpful resources

Announcements
September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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