How to use SUMIFS in power BI ( Customer wise MTD sale) when Customer code one table and Sale Dump

[proavinash]

How to use SUMIFS in power BI ( Customer wise MTD sale) when Customer code one table and Sale Dump other table.

Sale dump table

Customer code    Customer Name        date                    Region      zone      Qty       Sale Net

1000001                   Ramesh                 10-10-2018         North       NCR       30         183642

2

3

4

and so on

Customer Table

Customer Code      MTD Sale      QTD Sale     YTD Sale     Q1 Sale     Q2 Sale    Q3 Sale     Q4 Sale

1000001

2

3

4

and so on

[Kedar_Pande]

@proavinash 

Ensure there is a relationship between the Customer Table[Customer Code] and Sale Dump Table[Customer code].

MTD Sale = 
CALCULATE(
SUM('Sale Dump Table'[Sale Net]),
DATESMTD('Sale Dump Table'[date])
)

💌 If this helped, a Kudos 👍 or Solution mark ✅ would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[ThxAlot]

Quite some shxty rookies, like this one, order other around and take for granted that any others are supposed to help.

[proavinash]

NEED RESULT LIKE CUSTOMER TABLE.

[Anonymous · Accepted solution]

Hi, @proavinash 

Thanks for the reply from Kedar_Pande, ThxAlot and lbendlin. You can use the dax to achieve your need. By the way, note that there seems to be a problem with the MTD and QTD you posted for 1002 and 1003, the data in the following screenshot seems to be the correct result.

Measure: 

SaleMTD = 
VAR _todayYear =
    YEAR ( TODAY () )
VAR _todayQuarter =
    QUARTER ( TODAY () )
VAR _todayMonth =
    MONTH ( TODAY () )
VAR _date =
    SELECTEDVALUE ( Demand[Date] )
VAR _re =
    IF (
        YEAR ( _date ) = _todayYear
            && MONTH ( _date ) = _todayMonth
            && _date <= TODAY (),
        SUM ( Demand[Net Sale] ),
        0
    )
RETURN
    _re


SaleQTD = 
VAR _todayYear =
    YEAR ( TODAY () )
VAR _todayQuarter =
    QUARTER ( TODAY () )
VAR _todayMonth =
    MONTH ( TODAY () )
VAR _date =
    SELECTEDVALUE ( Demand[Date] )
VAR _re =
    IF (
        YEAR ( _date ) = _todayYear
            && QUARTER ( _date ) = _todayQuarter
            && _date <= TODAY (),
        SUM ( Demand[Net Sale] ),
        0
    )
RETURN
    _re


SaleYTD = 
VAR _todayYear =
    YEAR ( TODAY () )
VAR _todayQuarter =
    QUARTER ( TODAY () )
VAR _todayMonth =
    MONTH ( TODAY () )
VAR _date =
    SELECTEDVALUE ( Demand[Date] )
VAR _re =
    IF ( YEAR ( _date ) = _todayYear, SUM ( Demand[Net Sale] ), 0 )
RETURN
    _re

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[lbendlin]

Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

[proavinash]

NEED RESULT CUSTOMER WISE MTD SALE , QTD SALE, YTD SALE, THEN Q1, Q2 , Q3, Q4, LIKE DISPLAY IN CUSTOMER TABLE.

DATABASE TABLE.

Customer Code	Customer Name	Date	Region	Zone	Qty	Net Sale
10000001	Ramesh	10-10-2018	North	NCR	50	454645
10000002	Ram	10-11-2024	East	TN	45	343645
10000003	Shayam	12-19-2024	West	UP East	64	132323
10000004	Kumar Bisvas	10-13-2018	South	AP	35	4545464
10000005	Raju	10-14-2018	North	WB	67	2343432
10000006	Ram Gopal	10-15-2018	East	KTK	45	8735454
10000007	Kamal	10-16-2018	West	RAJ	75	9834738
10000008	Neel Nitin	10-17-2018	South	GUJ	76	763476
10000009	Suraj	10-18-2018	North	MUM	54	334455
10000010	Jyoti	10-19-2018	East	NCR	45	243545
10000006	Ram Gopal	10-15-2018	East	KTK	45	8735454
10000007	Kamal	10-16-2018	West	RAJ	75	9834738
10000008	Neel Nitin	10-17-2018	South	GUJ	76	763476
10000009	Suraj	10-18-2018	North	MUM	54	334455
10000010	Jyoti	10-19-2018	East	NCR	45	243545
10000001	Ramesh	10-10-2018	North	NCR	50	454645
10000002	Ram	10-11-2024	East	TN	45	343645
10000003	Shayam	12-19-2024	West	UP East	64	132323
10000004	Kumar Bisvas	10-13-2018	South	AP	35	4545464
10000005	Raju	10-14-2018	North	WB	67	2343432
10000006	Ram Gopal	10-15-2018	East	KTK	45	8735454
10000007	Kamal	10-16-2018	West	RAJ	75	9834738
10000008	Neel Nitin	10-17-2018	South	GUJ	76	763476
10000009	Suraj	10-18-2018	North	MUM	54	334455
10000010	Jyoti	10-19-2018	East	NCR	45	243545

RESULT

Customer Code	MTD SALE	QTD SALE	YTD SALE
10000001	0	0	0
10000002	264646	0	264646
10000003	0	687290	687290
10000004	0	0	0
10000005	0	0	0
10000006	0	0	0
10000007	0	0	0
10000008	0	0	0
10000009	0	0	0
10000010	0	0	0