Forum Discussion

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...

Anonymous

Not applicable

8 years ago

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
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
  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