Forum Discussion

vehau1's avatar
vehau1
Helper I
6 years ago
Solved

Custom Colums problem: prognosis

I have a fairly easy problem, but I dont get it right. I want to calculate "Prognosis"

Date-DateWvals--RunningTOtal--RunningPlannedTotal--Prognosis-
1.1.2020  1.1.202021002200blank
1.2.2020  1.2.202040004400blank
1.3.2020  blankblank6600X
1.4.2020  blankblank8800X2

 

Here, X should equal to 6200. 

 

X = RunningPlannedTotal + (RunningTotal-RunningPlannedTotal, at the last non-blank value of DateWvals)

X = 6600 + (4000-4400) = 6200. And so the X2 would yield: 8800 + (4000-4400) = 8400.

Earlier vals of Prognosis should be blank.

 

Please help!

Need Help
  • ryan_mayu's avatar
    ryan_mayu
    6 years ago

    vehau1 

     

    you can try below DAX coding to create a column.

     

    Column = 
VAR lastdate1= MAXX(FILTER(Sheet1,'Sheet1'[Date]<EARLIER(Sheet1[Date])&&Sheet1[-RunningTOtal-]<>"blank"),'Sheet1'[Date].[Date])
VAR lasttotal=MAXX(FILTER(sheet1,Sheet1[Date]=lastdate1),Sheet1[-RunningTOtal-])
VAR lastplantotal=MAXX(FILTER(sheet1,Sheet1[Date]=lastdate1),Sheet1[-RunningPlannedTotal-])
return 
if(lastdate1 =0,blank(),Sheet1[-RunningPlannedTotal-]+(lasttotal-lastplantotal))

2 Replies

  • ryan_mayu's avatar
    ryan_mayu
    Super User
    6 years ago

    vehau1 

     

    you can try below DAX coding to create a column.

     

    Column = 
VAR lastdate1= MAXX(FILTER(Sheet1,'Sheet1'[Date]<EARLIER(Sheet1[Date])&&Sheet1[-RunningTOtal-]<>"blank"),'Sheet1'[Date].[Date])
VAR lasttotal=MAXX(FILTER(sheet1,Sheet1[Date]=lastdate1),Sheet1[-RunningTOtal-])
VAR lastplantotal=MAXX(FILTER(sheet1,Sheet1[Date]=lastdate1),Sheet1[-RunningPlannedTotal-])
return 
if(lastdate1 =0,blank(),Sheet1[-RunningPlannedTotal-]+(lasttotal-lastplantotal))

    • vehau1's avatar
      vehau1
      Helper I
      6 years ago

      Thank you Ryan! Much appreciated. I based my solution off yours.