Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Line chart challenge

(Edited)
Hi,

 

I intend to create a single line chart that visualizes 2 set of values - OValue and Forecast_Value. (both in different tables)

 

The user is given an option to choose a Product ID using a slicer. Also the user chooses the Run ID from the given filter which provides PBI the anchor date > i.e. the visual will show data N days before and N after the anchor date. 

 

Using the anchor date (suppose T) to the product ID, I intend to display:

1. OValue for 3 days prior to the date T (i.e T-3 days) and

2. Forecast_Value for 10 days after the date T (i.e T+3 days)

 

Both should be on the same graph visual as follows (here I have chosen 3rd january as the anchor date - Blue line is for OValues and Orange line for Forecast_Value

 

 

( I tried uploading the PBIX but the portal is not supporting it) The images of the mock data are as follows
Ovalue Table

Forecast Value table

 

 

Need Help
Tips and Tricks

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Anonymous , with help from a date table and date from date table is used on axis

     

    Value_A = calculate(sum(Table[Value]), dateadd('Date'[Date],-10,day))

     

    Value_B = calculate(sum(Table[Value]), dateadd('Date'[Date],10,day))

     

    To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

  • v-kelly-msft's avatar
    v-kelly-msft
    Community Support
    5 years ago

    Hi  Anonymous ,

     

    First create a date table as slicer;

    Then create a measure a below:

    Measure = 
var _date=SELECTEDVALUE('Date table'[Date])
Return
IF(MAX('Table'[Date])>=_date&&MAX('Table'[Date])<=_date+10,SUM('Table'[Value B]),IF(MAX('Table'[Date])<_date&&MAX('Table'[Date])>=_date-10,SUM('Table'[Value A])))

    And you will see:

     

    For the related .pbix file,pls see attached.

     

    Best Regards,
    Kelly

    Did I answer your question? Mark my post as a solution!

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Thanks for the prompt response members . But the model is this:

    Suppose Value A is Sales which is in one table and Value B is Forecasted sales in the other table.
    The visual looks like this - assuming I have forecasted values for all dates.

    Here based on the date as the slicer, I intend to visualise Sales for T-10 days and Forecasted sales for T+10 days.

    Its yet to be solved.

    • v-kelly-msft's avatar
      v-kelly-msft
      Community Support
      5 years ago

      Hi  Anonymous ,

       

      Could you pls provide a sample .pbix file for test?

       

      Best Regards,
      Kelly

      Did I answer your question? Mark my post as a solution!