Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

How to create different formula for each month

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)

MonthBrand CategoryQ1Q2Q3Q4MTD 
1Pizza88076990844993931888
2Pizza67566516725662431446
3Pizza83227285901580481731
4Pizza64616603761569551367
5Pizza64046758937495461790
6Pizza77996174825073051081
7Pizza72757478729776551905
8Pizza92519966861568441948
9Pizza72766964845576631756
10Pizza95686524663589331313
11Pizza61329525822566701322
12Pizza60036818693980401867

 

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

MonthBrand CategoryEstimateFormula 
1Pizza                      2,936=(Q1 Month 1)/3=8807/3
2Pizza4322=MTD Month 1 + (Q1 Month 2 - MTD Month 1)/2=1888+(6756-1888)/2
3Pizza8322=Q1 Month 3=8322
4Pizza8662=Q1 Month 4 + (Q2 Month 4)/ 3))=6461+(6603/3)
5Pizza10466.5=Q1 Month 5 + MTD Month 4 + (Q2 Month 5 - MTD Month 4)/2=6404+1367+(6758-1367)/2
6Pizza13973=Q1 Month 6 + Q2 Month 6 =7799+6174
7Pizza17185.33333=Q1 Month 7 +Q2 Month 7 + ((Q3 Month 7)/3)=7275+7478+(7297/3)
8Pizza24477=Q1 Month 8 + Q2 Month 8 + MTD Month 7 + (Q3 Month 8 - MTD Month 7)/2=9251+9966+1905+(8615-1905)/2
9Pizza22695=Q1 Month 9 + Q2 Month 9 + Q3 Month 9=7276+6964+8455
10Pizza25704.66667=Q1 Month 10 + Q2 Month 10 + Q3 Month 10 + (Q4 Month 10 ) / 3))=9568+6524+6635+8933/3
11Pizza27873.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
12Pizza27800=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 

DAX

1 Reply

  • ERD's avatar
    ERD
    Icon for Community Champion rankCommunity Champion
    5 years ago

    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.