Monthly Transaction Count with Refund/Reversal

[swwong1]

Hi All

In my data, I have both sales and refunds and would like to count the number of items sold on a monthly basis. If it was bought and refunded in the same month, it should be 0 items in that month, If it was bought in month 1 then refund in month 2, month 1 should still be 1 item, and month 2 should be -1.

I have temporarily used a calculated column to say if sales amount is >0 then 1, otherwise -1 but I know this isn't good practice.

Is there a COUNTIF equivalent in Dax?

https://www.dropbox.com/s/xaqe1gd5zhud1ag/Count%20Number%20of%20Transactions.pbix?dl=0 

Many thanks!

[v-jayw-msft · Accepted solution]

Hi @swwong1 ,

You could calculate the sum of sales group by month and [Invoice No].

sum_sales =
CALCULATE (
    SUM ( SalesTable[Sales] ),
    FILTER (
        SalesTable,
        SalesTable[Invoice No.] = EARLIER ( SalesTable[Invoice No.] )
            && MONTH ( SalesTable[Date] ) = MONTH ( EARLIER ( SalesTable[Date] ) )
    )
)

Then count [Invoice No] with sum_sales > 0 minus count [Invoice No] with sum_sales < 0 and group by month.

_count =
VAR a =
    CALCULATE (
        DISTINCTCOUNT ( SalesTable[Invoice No.] ),
        FILTER (
            ALLSELECTED ( SalesTable ),
            SalesTable[sum_sales] > 0
                && FORMAT ( SalesTable[Date], "MMMM" ) = SELECTEDVALUE ( 'Date'[Month Name] )
        )
    )
VAR b =
    CALCULATE (
        DISTINCTCOUNT ( SalesTable[Invoice No.] ),
        FILTER (
            ALLSELECTED ( SalesTable ),
            SalesTable[sum_sales] < 0
                && FORMAT ( SalesTable[Date], "MMMM" ) = SELECTEDVALUE ( 'Date'[Month Name] )
        )
    )
RETURN
    a - b

Best Regards,

Jay

[amitchandak]

@swwong1 , Try a new column like

Column = var _mon = EOMONTH([Date],0)
var _cnt = SUMX(filter(SalesTable, [Invoice No.] =EARLIER([Invoice No.]) && EOMONTH([Date],0) = _mon), sign([Sales]))
return if(_cnt =0,0, SIGN([Sales]))

or

Column = var _mon = EOMONTH([Date],0)
var _cnt = SUMX(filter(SalesTable, [Invoice No.] =EARLIER([Invoice No.]) && EOMONTH([Date],0) = _mon), sign([Sales]))
return if(_cnt =0,0, blank() ))