Bug? Counting the value for the same number of days in the previous month.

[Portmone]

Hello, everyone!

I'm trying to calculate transactions for the same count of days for previous month.

I have a great formula 

Transactions MoM = CALCULATE([Transactions], DATEADD(FILTER(DATESMTD('Calendar'[Calendar day]), 'Calendar'[Calendar day] < TODAY()),-1,MONTH)),

but it's not working correctly on the last day of month (2021-08-31).... I get [Transactions] for full previous month (2021-07-01 - 2021-07-31), but expect [Transactions] for period from 01 to 30 day of month.

How can I fix that?

Correct amount which I expected is 9 642 542, but got 9 935 710.

[Portmone · Accepted solution]

Change formula to my own - 

1) Transactions = sum(Analytics[Transactions.])

2) Transactions MoM = CALCULATE(Analytics[Transactions], DATESBETWEEN('Calendar'[Calendar day], dateadd(FIRSTDATE('Calendar'[Calendar day]),-1,MONTH), dateadd(LASTDATE(Analytics[Pay day]),-1,month)))

3) Transactions MoM % = IFERROR(IF(ISBLANK([Transactions MoM]),BLANK(),([Transactions]-[Transactions MoM])/abs([Transactions MoM])),BLANK())

Here CALCULATE(Analytics[Transactions], DATESBETWEEN('Calendar'[Calendar day], dateadd(FIRSTDATE('Calendar'[Calendar day]),-1,MONTH), dateadd(LASTDATE(Analytics[Pay day]),-1,month)))

I get the transactions for the windowed days on my Calendar. Works fine for MoM (previous month and a month a year ago)

[Portmone]

If I change FILTER(DATESMTD('Calendar'[Calendar day]), 'Calendar'[Calendar day] < TODAY()) to 

FILTER(DATESMTD('Calendar'[Calendar day]), 'Calendar'[Calendar day] < TODAY() - 1)

get transactions for 1..29 days correctly 🙂 

Magic only if I make FILTER(DATESMTD('Calendar'[Calendar day]), 'Calendar'[Calendar day] < TODAY())