Skip to main content
cancel
Showing results for 
Search instead 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

Reply
WaXpEd
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.

 

IndexQuarterProjectCombo ProjectIndex ComboValueValue Earlier
1Q215666666Q2156666661100
2Q215666666Q2156666661200
3Q215666666Q2156666661300
4Q315666666Q21566666644060
5Q315666666Q2156666664500
6Q315666666Q2156666664600

 

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
amitchandak
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

View solution in original post

4 REPLIES 4
Greg_Deckler
Super User
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])

Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

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

Hi @WaXpEd ,

 

Are you looking for the follwoing solution as shown below:

q1.png

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

 

Thanks,

Pragati

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

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

Appreciate your Kudos!!

Proud to be a Super User!!

amitchandak
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

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

 

Thanks again.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors