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

[Anonymous]

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:

Date	Segment	Total	Desired Result
30/4/20	Segment 1	65	34
30/4/20	Segment 2	15	-2
29/4/20	Segment 1	31	19
29/4/20	Segment 2	17	9
28/4/20	Segment 1	12	0 (first dates result)
28/4/20	Segment 2	8	0 (first dates result)

Hope this makes sence! Over to your geniuses!

Thanks

Jo

[v-xicai]

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]

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]

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

[Anonymous]

Hi @Greg,

I didn't think I had accepted a solution, so must have done it in error.

I'm still having problems with the formula in returning a running total without the first entry being the full total. Are you able to help? 

Thanks

Jo

[Greg_Deckler]

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

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]

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