Forum Discussion

BlueBandit93's avatar
BlueBandit93
Frequent Visitor
4 years ago

Creating two timelines, comparing actual versus forecasted

Hi all

 

I have a set of data with cash balances per day

 

I am looking to have two graphs (or one overlapping if possible) with the following:

 

1. Showing actual balances for a period specified with a slicer (SLICER 1), showing the forecasted amount for 2 months 

2. Showing the actual balance over time for the period specified in the slicer, being able to compare the actual to the forecasted for those two months (if it occurred in the past)

 

I created a measure to set the max date of the SLICER1 selected, and another measure for MaxDate + 60 days

 

However, I am unable to specifiy that the second graph should show the date after SLICER1 MaxDate

 

Is there a way to do this?

 

Thanks in advance!

Need Help

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    BlueBandit93 , If you want to display more dates on axis than what you have selected on slicer, then you need the slicer on an independent date table

     

    //Date1 is independent Date table, Date is joined with Table
    new measure =
    var _min = maxx(allselected(Date1),Date1[Date])
    var _max = _min +60
    return
    calculate( sum(Table[Value]), filter('Date', 'Date'[Date] >=_min && 'Date'[Date] <=_max))

     

    Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI

  • BlueBandit93's avatar
    BlueBandit93
    Frequent Visitor
    4 years ago

    Hi amitchandak

     

    Thank you for the help.

     

    I added the measure and created an independent date table, however I am still missing one step i believe.

     

    Data below:

    Date1 = ADDCOLUMNS (CALENDAR(date(2022,01,01), date(2022,06,30))
    ,"Month Year", Format ([date], "MMM-YYYY")
    ,"Month Year sort", FORMAT([date], "YYYYMM")
    ,"Year", YEAR([Date])
    , "Qtr Year", FORMAT([date],"YYYY/QQ")
    )


    Table called "Table"
    with "Date" and "Balance" columns typed in

    Two tables are linked as follows:

     

    When adding a forecasted line graph:
    X-Axis is Date1.[Date]
    Y-Axis is Table.[Balance]
    However, when moving the slicer, the peirods remain the same for the forecast and the actual

    Is there a way to create one slicer 

    Thank you!