Calculate projection

[Sinclair]

Hi,

please for the following sample data, is it possible to calculate the cell's prevision by using the Month column as filter?

I'm not sure whether it is possible or not.

I want to calculate the cell's prevision of June, July and august (where the data are missing in the column "Cell")

For a selected period (for example from Feb. to June) in the filter (the column Month), 

the cell's prevision of June = [SUM(Cell)/SUM(Objective "where Cell is not null")]*(Objective of June)

Cell	Objective	Month	Year
5000	5500	1	2018
2546	3000	2	2018
3265	3000	3	2018
4587	4300	4	2018
1254	2000	5	2018
	5600	6	2018
	5200	7	2018
	6000	8	2018

May I kindly ask for your help?

Thank you

[Sinclair]

Hi,

please for the following sample data, is it possible to calculate the cell's prevision by using the Month column as filter?

I'm not sure whether it is possible or not.

I want to calculate the cell's prevision of June, July and august (where the data are missing in the column "Cell")

For a selected period (for example from Feb. to June) in the filter (the column Month), 

the cell's prevision of June = [SUM(Cell)/SUM(Objective "where Cell is not null")]*(Objective of June)

Cell	Objective	Month	Year
5000	5500	1	2018
2546	3000	2	2018
3265	3000	3	2018
4587	4300	4	2018
1254	2000	5	2018
	5600	6	2018
	5200	7	2018
	6000	8	2018

May I kindly ask for your help?

Thank you

[Anonymous]

Hi @Sinclair

Giving high level idea: Create a calculated column New_Cell as shown below and display this column in visuals in the place of "Cell" column.

New_Cell =

VARIABLE inCalc = <Your calculation for Projection>

IF ( ISBLANK(Cell) , inCalc, Cell)

Try and let us know if this works.

Thanks
Raj

[Sinclair]

Hi @Anonymous

thanks a lot for your proposition. I think it cannot be a calculated colomn because the value of the projection should be updated according to the Range selected in the filter.

In the filter we can select for example 

 Jan - June: and the projection of June should be calculated with the data of Jan to May

Feb - July: and the projection June and July be should be calculated with the data of Feb to May

...

The point is to be able to select diferents Range of Mouth and calculate the projection according to the Range selected

Thanks

Sinclair

[v-shex-msft]

Hi @Sinclair,

You can try to use below measure if it suitable for your requirement:

Sample =
VAR currYear =
    MAX ( Table[Year] )
VAR monthList =
    ALLSELECTED ( Table[Month] )
VAR lastMonth =
    MAXX ( ALLSELECTED ( Table[Month] ), [Month] )
VAR divi =
    DIVIDE (
        SUM ( Table[Cell] ),
        CALCULATE (
            SUM ( Table[Objective] ),
            FILTER (
                ALL ( Table ),
                [Year] = currYear
                    && [Month] IN monthlist
                    && [cell] <> BLANK ()
            )
        ),
        -1
    )
RETURN
    divi * SUM ( Table[Objective] )

Regards,

Xiaoxin Sheng