Change line graph visual based on whether date is future dated

[Shawry]

Hi, 

I'm wondering if it's possible to format future dated data points differently? For example, if I was to plot the 'Planned Leave #' using a line graph - is it possible to display the forecasted/future dated Planned Leave # as a dotted line? 

Thanks

[TomMartens]

Hey @Shawry ,

in addition to what @vicky_ and @fahadqadir3 , already mentioned, I create a measure for the future values that also starts with the month before the future starts, I do this to overcome the gap.
The Measure:

futureValues = 
var currentmonth = SELECTEDVALUE( 'DimDate'[MonthNumber] )
var lastPastMonth =
    CALCULATE(
        MAX( 'DimDate'[MonthNumber] )
        , ALLSELECTED( 'DimDate' )
        , ALL( 'DimDate'[past or future month] )
        , 'DimDate'[past or future month] = "past" 
    )
return
if( currentmonth >= lastPastMonth
    , CALCULATE(
        [SalesAmount (ms)]
        , ALL( 'DimDate' )
        , 'DimDate'[MonthNumber] = currentmonth
    )
    , BLANK()
)

Please be aware that the column future or past month from the calendar table has to reflect the use case. For this example it was simple, because I consider June the last "known" month, and July the first future month.

But this allows me to "transform" the top visual with the gap between June and July into a visual with a continuous line:

I tend to use a solid line because instead of a dotted line because sometimes there are past values I do not want to show as you can see from the table:

For this reason, I use a solid line that is visualized on top of the "past" values; if I used dotted lines, then the line would look a little awkward.

As @fahadqadir3 suggests, having two measures will solve the problem of rendering a dotted line on top of a solid line, but then you will have two measures that might increase the overall solution complexity.

Finally, you have to consider whether the monthly values represent values at the end of the month or at the beginning of the month. My example assumes a value at the end of the month. This consideration is relevant for the color of the line (the segment) between June and July

Hopefully, you will find this approach to avoid the gap useful.

Regards,

Tom

[Shawry]

Hi Tom, Thanks! This is exactly what I'm looking to do. My DAX isn't too great, and I'm having trouble reformatting your solution and applying it at a week level instead of monthly. Below, you'll see that no values are actually being returned in the column. 

My DAX formula is as follows:

futureValues = 
VAR currentweek = SELECTEDVALUE('Staff Data - Weekly'[WeekNumber])
VAR lastweek =
    CALCULATE(
        MAX('Staff Data - Weekly'[WeekNumber]),
        ALLSELECTED('Staff Data - Weekly'[Date]),
        ALL('Staff Data - Weekly'),
        'Staff Data - Weekly'[Past or future week] = "Past Week"
    )
RETURN
    IF(
        currentweek >= lastweek,
        CALCULATE(
            SUM('Staff Data - Weekly'[Planned Leave %]),  
            ALL('Staff Data - Weekly'[Date]),
            'Staff Data - Weekly'[WeekNumber] = currentweek
        ),
        BLANK()
    )

 Can you see where I'm going wrong?

Thanks

[fahadqadir3]

@Shawry You can create dotted line for future dates, please review the attached screenshot and pbix file. 

PastPlannedLeave = IF(MAX('Table'[Date]) <= TODAY(), MAX('Table'[Planned Leave #]), BLANK())

FuturePlannedLeave = IF(MAX('Table'[Date]) > TODAY(), MAX('Table'[Planned Leave #]), BLANK())

Planned Leaves.pbix

Hope it works.

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

[vicky_]

Try and create one measure for past dates, and one measure for future dates (you might need to tweak your measures so there isn't a big gap between the two measures. Then select your future dates measure in the format pane, and change the line style to dotted: