Forum Discussion

Sinclair's avatar
Sinclair
Frequent Visitor
8 years ago

Calculate projection

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 t...
Need Help
Sinclair's avatar
Sinclair
Frequent Visitor
8 years ago

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

Anonymous's avatar
Anonymous
Not applicable
8 years ago

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