Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

YTD combining data issue

Hi all,

 

I have several projects (in sample data only one project called CBLR) for which I have for every month in 2022 Actuals data (until 01.04.2022) and Forecast data (after 01.04.2022).

 

What I would like to achieve is a YTD trend line chart per project that shows the YTD data over the whole period. Hence, until 01.04.2022 I would need to see YTD Actuals and after that it should add Forecast data (YTD).

 

Note that the YTD period table will be manually updated every month (hence, next month it will be 01.05.2022).

 

I was before able with support of the forum to create a measure that shows on a full year Actuals and Forecast per month, but it failed for YTD values.

 

Below shared sample data :

 

https://1drv.ms/u/s!Anx9rs5Lmt-ilVxBSVTpdlXuYdXP?e=Px7bei 

speedramps 

Need Help
  • speedramps's avatar
    speedramps
    4 years ago

    Hi again ybz


    We meet again !

     

    Try this

    Click here to download my solution 


    I have added some date to test more than one project

    You need to delete the YTD table relationship and then add these 2 measure.
    I have added comments so can learn DAX

    YTD =
    // get the max date from the YTD table
    MAX('YTD Period'[YTD Period])

     

    Trend =
    // create a subset of dates <= YTD date
    VAR beforeytd = FILTER('Calendar','Calendar'[Date] <= [YTD] )
    // create a subset of dates > YTD date
    VAR afterytd = FILTER('Calendar','Calendar'[Date] > [YTD] )
    RETURN
    // get actuals for the subset of dates <= YTD date
    CALCULATE(
    SUM(Actuals[Actuals]),
    beforeytd
    )
    +
    // get the forecast for the subset of dates > YTD date
    CALCULATE(
    SUM('Latest Estimate'[LE]),
    afterytd
    )

     

    Create line graph with

    xaxis = Calendar [Date]

    Yaxis = Trend

    Legen = Project list [project]

     

    Please click thumbs up and accept as solution buttons.  Thank you ! 😎

     

     

     

     

     

  • speedramps's avatar
    speedramps
    4 years ago

    Hi again YBZ

     

    I have updated my example with the solution

    Click here to download my solution 


    I have added this DAX measure to get the YTD Trend
    and added lots of comments so you can learn DAX.
    I prefer to teach on this furum rather than just give solutions.

    Please click thumbs up and accept as solution button. Thank you ! 😎

     
    YTD trend =
    // get the end date for as each period as they are being drawn in the visual eg Jan, Feb, Mar
    VAR mydate = MAX('Calendar'[Date])
    RETURN
    // If the trend for the date is blank then do nothing
    // else use the ALL command to get the YTD trend
    IF(ISBLANK([Trend]), BLANK(),
    CALCULATE(
    [Trend],
    ALL('Calendar'),
    'Calendar'[Date] <= mydate
    ))
     
    You will still need these measures ....

    YTD date =
    // get the max date from the YTD table
    MAX('YTD Period'[YTD Period])

     

    Trend =
    // create a subset of dates <= YTD date
    VAR beforeytd = FILTER('Calendar','Calendar'[Date] <= [YTD date] )
    // create a subset of dates > YTD date
    VAR afterytd = FILTER('Calendar','Calendar'[Date] > [YTD date] )
    RETURN
    // get actuals for the subset of dates <= YTD date
    CALCULATE(
    SUM(Actuals[Actuals]),
    beforeytd
    )
    +
    // get the forecast for the subset of dates > YTD date
    CALCULATE(
    SUM('Latest Estimate'[LE]),
    afterytd
    )

     

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    I continue on this project and i made this

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi

     

    Try this

     

  • speedramps's avatar
    speedramps
    Super User
    4 years ago

    Hi again ybz


    We meet again !

     

    Try this

    Click here to download my solution 


    I have added some date to test more than one project

    You need to delete the YTD table relationship and then add these 2 measure.
    I have added comments so can learn DAX

    YTD =
    // get the max date from the YTD table
    MAX('YTD Period'[YTD Period])

     

    Trend =
    // create a subset of dates <= YTD date
    VAR beforeytd = FILTER('Calendar','Calendar'[Date] <= [YTD] )
    // create a subset of dates > YTD date
    VAR afterytd = FILTER('Calendar','Calendar'[Date] > [YTD] )
    RETURN
    // get actuals for the subset of dates <= YTD date
    CALCULATE(
    SUM(Actuals[Actuals]),
    beforeytd
    )
    +
    // get the forecast for the subset of dates > YTD date
    CALCULATE(
    SUM('Latest Estimate'[LE]),
    afterytd
    )

     

    Create line graph with

    xaxis = Calendar [Date]

    Yaxis = Trend

    Legen = Project list [project]

     

    Please click thumbs up and accept as solution buttons.  Thank you ! 😎

     

     

     

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      speedramps Anonymous 

      thanks a lot for sharing the solution and explaining the steps. It is really appreciated as a beginner 🙂

       

      I understand both your logic on how to get the actuals / forecast per month (based on YTD period). What I do not understand is how to get the actuals or forecast YTD (last column) as in below table from Anonymous 

       

       
      • speedramps's avatar
        speedramps
        Super User
        4 years ago

        Hi again YBZ

         

        I have updated my example with the solution

        Click here to download my solution 


        I have added this DAX measure to get the YTD Trend
        and added lots of comments so you can learn DAX.
        I prefer to teach on this furum rather than just give solutions.

        Please click thumbs up and accept as solution button. Thank you ! 😎

         
        YTD trend =
        // get the end date for as each period as they are being drawn in the visual eg Jan, Feb, Mar
        VAR mydate = MAX('Calendar'[Date])
        RETURN
        // If the trend for the date is blank then do nothing
        // else use the ALL command to get the YTD trend
        IF(ISBLANK([Trend]), BLANK(),
        CALCULATE(
        [Trend],
        ALL('Calendar'),
        'Calendar'[Date] <= mydate
        ))
         
        You will still need these measures ....

        YTD date =
        // get the max date from the YTD table
        MAX('YTD Period'[YTD Period])

         

        Trend =
        // create a subset of dates <= YTD date
        VAR beforeytd = FILTER('Calendar','Calendar'[Date] <= [YTD date] )
        // create a subset of dates > YTD date
        VAR afterytd = FILTER('Calendar','Calendar'[Date] > [YTD date] )
        RETURN
        // get actuals for the subset of dates <= YTD date
        CALCULATE(
        SUM(Actuals[Actuals]),
        beforeytd
        )
        +
        // get the forecast for the subset of dates > YTD date
        CALCULATE(
        SUM('Latest Estimate'[LE]),
        afterytd
        )