How to create different formula for each month

[Anonymous]

Hi,

I am new to Power BI and kindly need your help.

So I have this data (actually I have more than 1 brand category but for simplification I put only 1 brand)

Month	Brand Category	Q1	Q2	Q3	Q4	MTD
1	Pizza	8807	6990	8449	9393	1888
2	Pizza	6756	6516	7256	6243	1446
3	Pizza	8322	7285	9015	8048	1731
4	Pizza	6461	6603	7615	6955	1367
5	Pizza	6404	6758	9374	9546	1790
6	Pizza	7799	6174	8250	7305	1081
7	Pizza	7275	7478	7297	7655	1905
8	Pizza	9251	9966	8615	6844	1948
9	Pizza	7276	6964	8455	7663	1756
10	Pizza	9568	6524	6635	8933	1313
11	Pizza	6132	9525	8225	6670	1322
12	Pizza	6003	6818	6939	8040	1867

So from this data I want the result to look like this

Month	Brand Category	Estimate	Formula
1	Pizza	2,936	=(Q1 Month 1)/3	=8807/3
2	Pizza	4322	=MTD Month 1 + (Q1 Month 2 - MTD Month 1)/2	=1888+(6756-1888)/2
3	Pizza	8322	=Q1 Month 3	=8322
4	Pizza	8662	=Q1 Month 4 + (Q2 Month 4)/ 3))	=6461+(6603/3)
5	Pizza	10466.5	=Q1 Month 5 + MTD Month 4 + (Q2 Month 5 - MTD Month 4)/2	=6404+1367+(6758-1367)/2
6	Pizza	13973	=Q1 Month 6 + Q2 Month 6	=7799+6174
7	Pizza	17185.33333	=Q1 Month 7 +Q2 Month 7 + ((Q3 Month 7)/3)	=7275+7478+(7297/3)
8	Pizza	24477	=Q1 Month 8 + Q2 Month 8 + MTD Month 7 + (Q3 Month 8 - MTD Month 7)/2	=9251+9966+1905+(8615-1905)/2
9	Pizza	22695	=Q1 Month 9 + Q2 Month 9 + Q3 Month 9	=7276+6964+8455
10	Pizza	25704.66667	=Q1 Month 10 + Q2 Month 10 + Q3 Month 10 + (Q4 Month 10 ) / 3))	=9568+6524+6635+8933/3
11	Pizza	27873.5	=Q1 Month 11 + Q2 Month 11 + Q3 Month 11 + MTD Month 10 + (Q4 Month 11 - MTD Month 10)/2	=6132+9525+8225+1313+(6670-1313)/2
12	Pizza	27800	=Q1 Month 12 + Q2 Month 12 + Q3 Month 12 + Q4 Month 12	=6003+6818+6939+8040

Each month, the formula is different Q1 Month 1 means that it is in column Q1 from Month 1, etc.

Hope it's clear enough and hope you can help

Thank you 

[ERD]

Hi @Anonymous ,

you can try the measure below:

Estimate = 
VAR c_month = SELECTEDVALUE ( T[Month] )
VAR q1 = SELECTEDVALUE ( T[Q1] )
VAR q2 = SELECTEDVALUE ( T[Q2] )
VAR q3 = SELECTEDVALUE ( T[Q3] )
VAR q4 = SELECTEDVALUE ( T[Q4] )
VAR prev_month =
    CALCULATE (
        MAX ( T[Month] ),
        FILTER ( ALL ( T[Month] ), T[Month] < c_month )
    )
VAR prev_mtd = CALCULATE ( MAX ( T[MTD ] ), T[Month] = prev_month )
RETURN
    SWITCH (
        TRUE (),
        c_month = 1, SELECTEDVALUE ( T5[Q1] ) / 3,
        c_month = 2, prev_mtd + ( q1 - prev_mtd ) / 2,
        c_month = 3, q1,
        c_month = 4, q1 + q2 / 3,
        c_month = 5, q1 + prev_mtd + ( q2 - prev_mtd ) / 2,
        c_month = 6, q1 + q2,
        c_month = 7, q1 + q2 + q3 / 3,
        c_month = 8, q1 + q2 + prev_mtd + ( q3 - prev_mtd ) / 2,
        c_month = 9, q1 + q2 + q3,
        c_month = 10, q1 + q2 + q3 + q4 / 3,
        c_month = 11, q1 + q2 + q3 + prev_mtd + ( q4 - prev_mtd ) / 2,
        c_month = 12, q1 + q2 + q3 + q4,
        0
    )

If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.