Help with calculated column in PBI

[AldoMF]

I need to calculate actuals in Power BI (I TIPYCALLY USE EXCEL WITH PIVOTS), to calculate actuals this is the logic: Take the value in the intersection of Period 2023Q2, cycle 2023Q2 in source 1 Take the value in the intersection of Period 2023Q1, cycle 2023Q2 in source 2 Take the value in the intersection of Period 2022Q4, cycle 2023Q1 in source 2 Take the value in the intersection of Period 2022Q3, cycle 2022Q4 in source 2 Take the value in the intersection of Period 2022Q2, cycle 2022Q3 in source 2 Take the value in the intersection of Period 2022Q1, cycle 2022Q2 in source 2, etc

Source 2 has 14 cycles and 26 periods (14 for history and 12 for forecast)
Source 1 has only 1 cycle (current or latest) and 14 periods (all of them history)

The source data has also other 5 'sources' or models with information but to get data I need only those 2, so actuals for all 7 sources need to be the same as the logic explained above

I need this to be done in a dynamic way because every quarter I get new data so the cycles will update and the periods as well for all sources, however the structure won't. Source 1 will still have 1 cycle and 14 historical periods, all the other sources will have 14 cycles and 26 periods (14 history and 12 forecast)

any advice helps, I've tried chat GPT but I have gotten nowhere

[parry2k]

@AldoMF can you check desired output, period seems to be wrong.

[AldoMF]

sorry for the confusion, it's correct now in the previous reply

[AldoMF]

This is a sample data with in a pivot table from excel:

Sum of Units	Period
Source	Cycle	2020Q1	2020Q2	2020Q3	2020Q4	2021Q1	2021Q2	2021Q3	2021Q4	2022Q1	2022Q2	2022Q3	2022Q4	2023Q1	2023Q2	2023Q3	2023Q4	2024Q1	2024Q2	2024Q3	2024Q4	2025Q1	2025Q2	2025Q3	2025Q4	2026Q1	2026Q2
Source 2	2020Q1	757	772	501	791	661	538	569	784	671	551	715	517	552	714	502	798	761	536	566	650	636	790	518	573
Source 2	2020Q2	674	675	627	567	693	745	685	580	730	744	665	596	753	580	742	681	682	605	535	741	734	694	779	697
Source 2	2020Q3	611	554	775	506	640	560	606	583	785	526	740	747	727	777	729	737	711	715	602	591	771	529	644	647
Source 2	2020Q4	561	682	727	586	767	578	673	603	731	596	723	624	682	717	528	554	752	696	570	699	512	630	581	557
Source 2	2021Q1	549	609	685	696	749	730	741	800	786	789	557	579	531	579	741	501	673	784	624	715	665	593	630	755	643	534
Source 2	2021Q2	650	694	612	681	766	791	653	554	732	627	698	663	769	627	618	520	551	589	769	709	732	534	761	520	640	543
Source 2	2021Q3	591	738	537	761	785	714	518	700	721	746	771	653	626	697	716	602	517	521	739	674	764	727	721	728	593	644
Source 2	2021Q4	529	739	510	714	737	701	680	756	664	688	612	521	740	590	657	545	667	606	634	547	647	571	789	690	646	502
Source 2	2022Q1	679	762	770	565	752	791	516	506	787	526	785	715	509	516	648	637	670	752	727	653	600	623	686	782	782	638
Source 2	2022Q2	504	796	578	780	684	744	754	630	763	725	644	622	659	783	695	544	622	516	522	692	596	743	520	551	651	640
Source 2	2022Q3	594	654	787	794	679	577	800	538	525	709	519	769	548	515	726	782	603	571	541	572	552	566	747	615	657	599
Source 2	2022Q4	691	785	699	633	556	522	699	765	794	590	729	540	580	747	585	657	507	690	589	524	583	686	610	625	571	557
Source 2	2023Q1	556	752	783	526	536	558	555	748	602	541	678	518	520	542	622	799	757	756	621	684	748	666	541	568	740	543
Source 2	2023Q2	776	705	712	769	675	660	776	668	706	557	744	583	733	777	519	775	552	669	614	696	509	650	501	664	531	524
Source 1	2023Q2	577	528	690	642	726	681	735	659	616	536	719	707	661	748

 

The desired outcome is this: (also marked in blue in the above table)

Actuals
Period	Units
2020Q1	674
2020Q2	554
2020Q3	727
2020Q4	696
2021Q1	766
2021Q2	714
2021Q3	680
2021Q4	506
2022Q1	763
2022Q2	709
2022Q3	729
2022Q4	518
2023Q1	733
2023Q2	748

[parry2k]

@AldoMF Please provide some data with the expected output Read this post to get your answer quickly.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490