How to sum sales with comparing 2 column dates

[harib]

HI 

I would like to to get the sum of PROFITABLE_OUTLETS with comparing "DATE_MOC" and "ECO_MOC_DATE" dates fields  .  

Below formula used in Tableau.

PO= SUM(IF (YEAR([Date_Moc]) = YEAR([ ECO_MOC_Date])

and MONTH([Date_Moc]) = MONTH([ ECO_MOC_Date]))

THEN  [PROFITABLE_OUTLETS] ELSE 0 END)

If i implement same logic in powerbi , not getting "ECO_MOC_1" field in year and month section.

PO =
VAR PO=
CALCULATE(SUM([PROFITABLE_OUTLETS]),
AND(
MONTH([Date_Moc])=MONTH(XXXXX),
YEAR([Date_Moc])=YEAR(XXXXX)))
RETURN IF(ISBLANK(PO),0,PO)

Please let me know how to get this

Thanks in advance

[v-jingzhang · Accepted solution]

Hi @harib 

If you need a new column, you could try:

PO Column =
IF (
    YEAR ( 'Table'[Date_Moc] ) = YEAR ( 'Table'[ECO_MOC_Date] )
        && MONTH ( 'Table'[Date_Moc] ) = MONTH ( 'Table'[ECO_MOC_Date] ),
    'Table'[PROFITABLE_OUTLETS],
    0
)

 

If you need a measure, you could try:

PO Measure = 
IF (
    MONTH ( SELECTEDVALUE ( 'Table'[Date_Moc] ) )
        = MONTH ( SELECTEDVALUE ( 'Table'[ECO_MOC_Date] ) )
        && YEAR ( SELECTEDVALUE ( 'Table'[Date_Moc] ) )
            = YEAR ( SELECTEDVALUE ( 'Table'[ECO_MOC_Date] ) ),
    SUM ( 'Table'[PROFITABLE_OUTLETS] ),
    0
)

Is this what you want?

Community Support Team _ Jing Zhang
If this post helps, please consider Accept it as the solution to help other members find it.