Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Calculated Column between two figures based on date and a filtered column.

Hi, I have a data set that is a sum of a total, rather than a total of the difference from the previous date and I need to calculate the difference between the two figures based on the previous dates total and filter it on a second column and identify the first date and record that as zero to give a starting figure.

 

The data set looks something like this:

 

DateSegmentTotalDesired Result
30/4/20Segment 16534
30/4/20Segment 215-2
29/4/20Segment 13119
29/4/20Segment 2179
28/4/20Segment 1120 (first dates result)
28/4/20Segment 280 (first dates result)

 

Hope this makes sence! Over to your geniuses!

 

Thanks

 

Jo

Need Help

6 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    6 years ago

    See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/339586

     

    In your case, something like:

     

    Column = 
  VAR __Current = [Total]
  VAR __PreviousDate = MAXX(FILTER('Table',[Segment]=EARLIER([Segment]) && [Date] < EARLIER([Date])),[Date])
  VAR __Previous = MAXX(FILTER('Table',[Segment]=EARLIER([Segment]) && [Date] = __PreviousDate),[Total])
RETURN
  __Current - __Previous

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thats great, worked a charm to calculate the difference which was just what i needed. 

       

      The only thing that is missing is setting the first figure in the date sequence to '0'.

       

      Any ideas?

       

      Thanks

       

      Jo

  • v-xicai's avatar
    v-xicai
    Community Support
    6 years ago

    Hi Anonymous ,

     

    You may create formula like DAX below.

     

     

    Column:  MinDate = MIN(Table1[Date])



Measure:

Diff =

VAR _LastDate=CALCULATE(MAX(Table1[Date]), FILTER(ALLSELECTED(Table1),Table1[Date]<MAX(Table1[Date])))

VAR _CurentTotal=CALCULATE(SUM(Table1[Total]),FILTER(ALLSELECTED(Table1),Table1[Segment]=MAX(Table1[Segment])&&Table1[Date]=MAX(Table1[Date])))

VAR _LastTotal=CALCULATE(SUM(Table1[Total]),FILTER(ALLSELECTED(Table1),Table1[Segment]=MAX(Table1[Segment])&&Table1[Date]=_LastDate))

RETURN

IF(MAX(Table1[Date])=MAX(Table1[MinDate]),0,IF(MAX(Table1[Date])>MAX(Table1[MinDate]),_CurentTotal-_LastTotal,0))

     

     

     

     

     

     

     

     

     

     

    Best Regards,

    Amy 

     

    Community Support Team _ Amy

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

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi v-xicai and Greg_Deckler 

       

      Im still strugling to make this work. The solution given by Greg on the calcualtion works great, and gives the correct outcome, but is missing the return to zero formula that Amy added to the end of her calculation.

       

      I have amalgamted the two here:

       

       

      Mailing List Subscriptions = 
VAR __Current = [Total Subscribers]

VAR __PreviousDate = MAXX(FILTER('Mailing List',[Segment  Name]=EARLIER([Segment  Name]) && [Date] < EARLIER([Date])),[Date])

VAR __Previous = MAXX(FILTER('Mailing List',[Segment  Name]=EARLIER([Segment  Name]) && [Date] = __PreviousDate),[Total Subscribers])

RETURN IF(MAX('Mailing List'[Date])=MAX('Mailing List'[MinDate]),0,IF(MAX('Mailing List'[Date])>MAX('Mailing List'[MinDate].[Date]),__Current - __Previous,0))

       

      But the calculation is now just returning a zero. Can you help?

       

      Many thanks

       

      Jo

       

      • Greg_Deckler's avatar
        Greg_Deckler
        Community Champion
        6 years ago

        Anonymous - Sorry, clarifying here, you are still having issues? I ask because there is an accepted solution so I just wanted to be sure you were still having problems. Please @ me in your response so I don't miss it.