Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
YBZ
Helper III
Helper III

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 

3 ACCEPTED SOLUTIONS
Anonymous
Not applicable

speedramps
Super User
Super User

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

 

 

 

 

 

View solution in original post

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
)

 

View solution in original post

7 REPLIES 7
speedramps
Super User
Super User

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 @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 

YBZ_0-1652853205286.png

 

 

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
)

 

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

 

Thank you.

Anonymous
Not applicable

I continue on this project and i made this

JamesFr06_0-1652824022403.png

 

Anonymous
Not applicable

Hi

 

Try this

JamesFr06_0-1652815801880.png

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.