Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Bilal_321321
Helper I
Helper I

Help converting Excel formula to DAX

Hi,

 

I have the excel formula below but am struggling to convert into DAX format. The formula is:

=SUMIFS(D:D,A:A,"<="&A2,B:B,B2,C:C,C2)

 

I am trying to get a year to date sum based on period (YYYYMM) and then 2 columns identifying Cost Centre and Cost Type.

 

Has anyone got an idea how i should do this?

 

Thanks

 

1 ACCEPTED SOLUTION
v-huizhn-msft
Employee
Employee

Hi @Bilal_321321,

The formula =SUMIFS(DSmiley Very Happy,A:A,"<="&A2,B:B,B2,C:C,C2) is converted into DAX as follows.

Create a measure in your Power BI model.

Measure =
CALCULATE (
    SUM ( Table[D] ),
    FILTER (
        Table,
        AND ( Table[A] <= Table[A2] && Table[B] = Table[B2], Table[C] = Table[C2] )
    )
)


Table[A], Table[B],Table[C] and Table[D] are column in Table, while Table[A2], Table[B2] and Table[C2] are fixed value in Table. Please replace them to yours, you will get expected result.

Best Regards,
Angelia

View solution in original post

3 REPLIES 3
v-huizhn-msft
Employee
Employee

Hi @Bilal_321321,

The formula =SUMIFS(DSmiley Very Happy,A:A,"<="&A2,B:B,B2,C:C,C2) is converted into DAX as follows.

Create a measure in your Power BI model.

Measure =
CALCULATE (
    SUM ( Table[D] ),
    FILTER (
        Table,
        AND ( Table[A] <= Table[A2] && Table[B] = Table[B2], Table[C] = Table[C2] )
    )
)


Table[A], Table[B],Table[C] and Table[D] are column in Table, while Table[A2], Table[B2] and Table[C2] are fixed value in Table. Please replace them to yours, you will get expected result.

Best Regards,
Angelia

MFelix
Super User
Super User

Hi @Bilal_321321,

 

You should use a Calculated formula in order to achieve the expected result, however without  details you should have anything like this:

Measure =
CALCULATE (
    SUM ( Table[Column1] ),
    VALUES ( Table[Column2] ) <= MAX ( Table[Column2] ),
    VALUES ( Table[Column3] ) = MAX ( Table[Column3] )
)

Calculate allows you to make several complex aggregations (sum, averages, ...) and apply filters and slicing to the formula. Check this link but be aware that you also need to see the rest of DAX, you need to have an understanding of row and table context.

 

Chek the SQLBI to get information and training.

 

Again without additional data cannot give you the expected result.

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



TomMartens
Super User
Super User

Hey, 

 

I'm pretty sure that it will be possible to re-write the Excel-Formula, but I would recommend that you change your model in accordance to some best practice modeling.

 

Almost everything for time/date related can be found here

www.daxpatterns.com/time-patterns

 

Hopefully this gets you started

 

Regards

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.