Forum Discussion

vehau1's avatar
vehau1
Helper I
6 years ago
Solved

Custom columns problem

an hour ago

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
  • amitchandak's avatar
    amitchandak
    6 years ago

    vehau1 , to me it seems like current-day value - last day value one column _ last day value on another column

     

    In the measure, you can get last day value like

    Last Day Non Continous = CALCULATE(sum('order'[Qty]),filter(all('Date'),'Date'[Date] =MAXX(FILTER(all('Date'),'Date'[Date]<max('Date'[Date])),Table['Date'])))
    Day behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Day))

     

    As a new column, you can try like

    RunningTOtalast day = maxx(filter(Table,[Date] =earlier([Date] )-1),[RunningTOtal])

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    vehau1 , to me it seems like current-day value - last day value one column _ last day value on another column

     

    In the measure, you can get last day value like

    Last Day Non Continous = CALCULATE(sum('order'[Qty]),filter(all('Date'),'Date'[Date] =MAXX(FILTER(all('Date'),'Date'[Date]<max('Date'[Date])),Table['Date'])))
    Day behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Day))

     

    As a new column, you can try like

    RunningTOtalast day = maxx(filter(Table,[Date] =earlier([Date] )-1),[RunningTOtal])

  • dax's avatar
    dax
    Community Support
    6 years ago

    Hi vehau1 , 

    You could try below measure to see whether it work or not.

    DateDateWvals-RunningTOtal-RunningPlannedTotal--Prognosis-

    Wednesday, January 1, 2020 Wednesday, January 1, 2020 2100 2200  
    Thursday, January 2, 2020 Thursday, January 2, 2020 4000 4400  
    Friday, January 3, 2020     6600  
    Saturday, January 4, 2020     8800   
    Measure 2 = var  maxd=CALCULATE(max(t1[DateWvals-]),ALLSELECTED(t1))  return if(MIN(t1[DateWvals-]) =BLANK(),SUM(t1[RunningPlannedTotal-])+CALCULATE(SUM(t1[RunningTOtal-])-SUM(t1[RunningPlannedTotal-]), filter (ALLSELECTED(t1),t1[DateWvals-]=maxd)), BLANK())

     

    Best Regards,
    Zoe Zhi

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.