Calculate volume for forecast and actual

[Anonymous]

I have financial year from nov - oct,suppose i am in june month and selected jun from month my actuals should be coming from table fcjun2021,forecast coming from table fcmay2021 in both the tables month is same i.e june.

How can i achieve it.

[v-xiaotang]

Hi @Anonymous 

First, you can get the selected month from the filter, then use calculate (sum (xx), filter (xxx)) to obtain data from table fcjun2021 for calculation, and then use the time intelligence function to calculate forecast coming from table fcmay2021. If you don't understand how to do it, you can share the sample data for further help.

Best Regards,

Community Support Team _Tang

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

[Anonymous]

fcjun2021	nov	dec	jan	feb	mar	apr	may	jun	jul	aug	sept	oct
a	100	200	300	400	500	600	700	800	900	1000	1500	2000
b	50	60	70	80	90	100	110	120	130	140	150	160
c	10	20	30	40	50	60	70	80	90	100	110	120
volume								333

fcmay2021	nov	dec	jan	feb	mar	apr	may	jun	jul	aug	sept	oct
a	100	200	300	400	500	600	700	600	900	1000	1500	2000
b	50	60	70	80	90	100	110	160	130	140	150	160
c	10	20	30	40	50	60	70	80	90	100	110	120
volume								768

here the month jun actual is 333 (avg of dept a,b,c) and forcast should be 768 that is coming from previous month so when i select month jun and dept a it should be 800 other than a,b,c i need to display  333 as actual ,fc will be change according dept, how can i achieve it?

[v-xiaotang]

Hi @Anonymous 

I've solved the first question now, but I dont know what the sentence bellow means. how we get 768?

 forcast should be 768 that is coming from previous month 

figure bellow is my current calculation result, 

Best Regards,

Community Support Team _Tang

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

[Anonymous]

how you achieve it ?

[v-xiaotang]

Hi @Anonymous 

sample file I attached below.

-

besides, you need to unpivot your table in desktop firstly,

result

2. the type of [nov, dec, jan...] is text and we cannot sort it in visual, so we need to add this table bellow, then merge it with the other 2 tables, 

then use Sort by column,

3. create the measure,

MeasureActual = 
    IF(ISINSCOPE(fcjun2021[dept]),
        SUM(fcjun2021[Value]),
        IF(MIN(fcjun2021[month])=SELECTEDVALUE(monthFilter[month]),
            IF(ISBLANK([M_selected_dept]),
                DIVIDE(
                    CALCULATE(SUM(fcjun2021[Value]),ALLEXCEPT(fcjun2021,fcjun2021[month])),
                    COUNT(deptFilter[dept])),
                CALCULATE(MIN(fcjun2021[Value]),FILTER(ALL(fcjun2021),fcjun2021[month]=[M_selected_month]&&fcjun2021[dept]=[M_selected_dept]))
    )))

hope it helps!

Best Regards,

Community Support Team _Tang

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

[Anonymous]

But for me both fcjun2021 and fcmay2021 are in same sheet not diff tables

[Anonymous]

share the measure what you written.

[Anonymous]

768 is forecast for present month and taking from previous month

[v-xiaotang]

Hi @Anonymous 

thanks for your reply, I know 768 is forecast value, but what's the calculation logic of it? 

Best Regards,

Community Support Team _Tang

[Anonymous]

oh that is also avg by mistake i mentioned as sum that should be 256. also you can share dax for the above.

[amitchandak]

@Anonymous , Both Tables should be unpivoted and months should be in rows. (you can create a date from that.

Using common date/month table you can analyze them together

https://radacad.com/pivot-and-unpivot-with-power-bi