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).   Wh...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi

     

    Try this

     

  • 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
    )

     

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
    )

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      thanks a lot, I copied it to my data source and it is working! 

       

      • speedramps's avatar
        speedramps
        Super User
        4 years ago

        Thank you.