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
taylorb
Helper I
Helper I

Close Gap Between Measures on Stacked Area Chart

I have 10 measures in a Stacked Area chart, in two sets of 5. One set is anything less than and equal to today and the other set is anything forcasted or greater than today. My issue is that the two measures create a gap in the visual between today and tomorrow. Is there a way to close this gap? If I make both measures where today is in the data then they stack up and it looks worse...

 

taylorb_0-1597423239183.png

 

1 ACCEPTED SOLUTION

hi  @taylorb 

Just adjust the Forecasted measure as below:

Forecasted = IF(MAX('Date'[Date])= TODAY(),[Historical],
CALCULATE(
    DISTINCTCOUNT(AssignmentsRef[ID]), 
    Filter(HistoricalTOAAssignmentsRef, 
    ([STATUS] = "Cleared to Start"  || ([STATUS] = "Cancelled" && AssignmentsRef[WAS_NEVER_STARTED] = false)) &&
    AssignmentsRef[TYPE] = "Long Term" &&
    AssignmentsRef[ActiveDate] > TODAY() &&
    AssignmentsRef[ActiveDate] <= TODAY()+14 &&
    AssignmentsRef[LOB] = "Manufacturing"
    ))
)

 

'Date'[Date] is the field which is in X-axis of Stacked Area Chart. if the date field in in X-axis of Stacked Area Chart is AssignmentsRef[ActiveDate], you just use it instead of 'Date'[Date].

 

Regards,

Lin

Community Support Team _ Lin
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

9 REPLIES 9
fhill
Resident Rockstar
Resident Rockstar

 

Can you post some sample data and your DAX forumlas?  My first thought is to alter the "Past Values DAX" to somehow also include +1 Day in the future Data.  So if you were looking at Past data for 8/10-14, change the code so it 'fakes' a 15th date = 8/15 from your future data?  (Or Duplicates 8/14 data for 8/15 in the Past if you can't pull from the future data?)

 

FOrrest

 

 




Did I answer your question, or help you along the way?
Please give Kudos or Mark as a Solution!


https://www.linkedin.com/in/forrest-hill-04480730/

Proud to give back to the community!
Thank You!




DAX Examples:

 

Forecasted = 
CALCULATE(
    DISTINCTCOUNT(AssignmentsRef[ID]), 
    Filter(HistoricalTOAAssignmentsRef, 
    ([STATUS] = "Cleared to Start"  || ([STATUS] = "Cancelled" && AssignmentsRef[WAS_NEVER_STARTED] = false)) &&
    AssignmentsRef[TYPE] = "Long Term" &&
    AssignmentsRef[ActiveDate] > TODAY() &&
    AssignmentsRef[ActiveDate] <= TODAY()+14 &&
    AssignmentsRef[LOB] = "Manufacturing"
    ))
Historical = 
CALCULATE(
    DISTINCTCOUNT(AssignmentsRef[ID]), 
    Filter(HistoricalTOAAssignmentsRef, 
    ([STATUS] = "Cleared to Start"  || ([STATUS] = "Cancelled" && AssignmentsRef[WAS_NEVER_STARTED] = false)) &&
    AssignmentsRef[TYPE] = "Long Term" &&
    AssignmentsRef[ActiveDate] <= TODAY() &&
    AssignmentsRef[LOB] = "Manufacturing"
    ))

 

They are literally the same data, just want the line to change to dotted after Today(), if I make both sets where they have Today() in their data then it stacks all the lines together on one day and looks way worse than the gap. See this example where I had one of the forecasted measures be >= Today() instead of just >

powerbi.png

hi  @taylorb 

Just adjust the Forecasted measure as below:

Forecasted = IF(MAX('Date'[Date])= TODAY(),[Historical],
CALCULATE(
    DISTINCTCOUNT(AssignmentsRef[ID]), 
    Filter(HistoricalTOAAssignmentsRef, 
    ([STATUS] = "Cleared to Start"  || ([STATUS] = "Cancelled" && AssignmentsRef[WAS_NEVER_STARTED] = false)) &&
    AssignmentsRef[TYPE] = "Long Term" &&
    AssignmentsRef[ActiveDate] > TODAY() &&
    AssignmentsRef[ActiveDate] <= TODAY()+14 &&
    AssignmentsRef[LOB] = "Manufacturing"
    ))
)

 

'Date'[Date] is the field which is in X-axis of Stacked Area Chart. if the date field in in X-axis of Stacked Area Chart is AssignmentsRef[ActiveDate], you just use it instead of 'Date'[Date].

 

Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anand24
Super User
Super User

Hi @taylorb ,

 

You need to decrease the inner padding from format:

Format -> X-Axis -> Decrease Inner Padding %

 

Give a thumbs up if this post helped you in any way and mark this post as solution if it solved your query !!!

Pragati11
Super User
Super User

Hi @taylorb ,

 

Seeing the screenshot that you shared, it shows that one of your measure calculations has value till August 14 and the other one has value from August 15 to the last value on the x-axis.

 

Click on your visual, under format options on visualisation pane, go to x-axis option, there is a padding option. Check that if that helps.

 

Thanks,

Pragati

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Proud to be a Super User!!

It doesnt look like there is a padding option for this visual

Hi @taylorb ,

 

Apologies for this. There is a Category Width option which you can check as highlighted below:

Pragati11_0-1597425365947.png

 

Thanks,

Pragati

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Proud to be a Super User!!

pranit828
Community Champion
Community Champion

Hi @taylorb 

 

Do you have time component in the date column? that might be causing it otherwise I can't think of a way it can be done.

 

Did I resolve your issue? Mark my post as a solution! Appreciate your Kudos, Press the thumbs up button!!

 

Regards,
Pranit

 

 

 

 





PBI_SuperUser_Rank@1x.png


Hope it resolves your issue? 
Did I answer your question? Mark my post as a solution!

Appreciate your Kudos, Press the thumbs up button!!
Linkedin Profile

There is not a time component, only date.

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.