DAX Column & SUMIFS with Earlier Quarter

[WaXpEd]

Hi all,

I'm trying since 4 hours from now to achieve this formula. Without any success...

I'm trying with earlier function to retrieve the total amount into a quarter from a previous quarter in the column.

Index	Quarter	Project	Combo Project	Index Combo	Value	Value Earlier
1	Q2	15666666	Q215666666	1	10	0
2	Q2	15666666	Q215666666	1	20	0
3	Q2	15666666	Q215666666	1	30	0
4	Q3	15666666	Q215666666	4	40	60
5	Q3	15666666	Q215666666	4	50	0
6	Q3	15666666	Q215666666	4	60	0

In Excel I woul have perform SUMIFS on "Value Earlier" column with the argument "Q2", but this is not possible in PowerBI as row is referenced under "Q3".

I can't plug a specfic number under the earlier function to take the previous "Index Combo" Value, as I can have multiple values in each quarter for each projects.

Thanks in advance, I think I've explored all combination of earlier over there.

WaX.

[Greg_Deckler]

Perhaps:

Value Earlier Column = 
  VAR __Quarter = [Quarter]
  VAR __PreviousQuarter = 
    SWITCH(__Quarter,
      "Q1","Q4",
      "Q2","Q1",
      "Q3","Q2",
      "Q1"
    )
RETURN
  SUMX(FILTER('Table',[Quarter] = __PreviousQuarter),[Value])

[Pragati11]

Hi @WaXpEd ,

Are you looking for the follwoing solution as shown below:

If this helps and resolves the issue, appreciate a Kudos and mark it as a Solution! 🙂

Thanks,

Pragati

[amitchandak · Accepted solution]

Try new columns like

Quarter No = right([Quarter],1)

Value Earlier = sumx(filter(table,table[Project]=earlier(table[Project]) && [Quarter No] =earlier([Quarter No])-1),[Value])

But if want index 4 only to have this new value , then additional logic need to be added to find min of the qtr

[WaXpEd]

Thanks a lot @amitchandak very helpful, it works now. Obvious with only numbers now you've spotted this! 

Thanks again.