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.2020 2100 2200 b...
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))

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.