YTD combining data issue

[YBZ]

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 

[Anonymous · Accepted solution]

Hi

Try this

[speedramps · Accepted solution]

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 · Accepted solution]

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 ! 😎

[YBZ]

@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 · Accepted solution]

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

)

[YBZ]

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

[speedramps]

Thank you.

[Anonymous]

I continue on this project and i made this

[Anonymous · Accepted solution]

Hi

Try this