Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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
Create line graph with
xaxis = Calendar [Date]
Yaxis = Trend
Legen = Project list [project]
Please click thumbs up and accept as solution buttons. Thank you ! 😎
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 ! 😎
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
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
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 ! 😎
thanks a lot, I copied it to my data source and it is working!
Thank you.
I continue on this project and i made this
Hi
Try this
User | Count |
---|---|
124 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
116 | |
102 | |
71 | |
61 |