cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## DAX Column & SUMIFS with Earlier Quarter

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.

1 ACCEPTED SOLUTION
Super User

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

4 REPLIES 4
Super User

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])``````

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Super User

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

Best Regards,

Pragati Jain

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Proud to be a Super User!!

Super User

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

Frequent Visitor

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

Thanks again.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.