DAX calculate yesterday data but ignoring if date is in another month

[veasnamuch]

I would like to create caluclaiton formula to make a comparison between today vs yesterday data. But when day fall on 1th of the month, yesterday value is calculated based on the last day of last month. I prefered to get 0 or blank value.

I create a formula as below 

MVT_INTL (TDAY):=calculate('Movements'[MVT_INTL], DATEADD('Calendar'[Date], 0, DAY))

MVT_INTL (YDAY):=calculate('Movements'[MVT_INTL], DATEADD('Calendar'[Date], -1, DAY))

How can I get when Date is 1th of the month, the MVT_INTL (YDAY) equals 0 or BLANK(). Means that if the day is 01-Mar-2022, the calculation for MVT_INTL (YDAY) should be 0 or BLANK()

Best regards,

Veasna

[Jihwan_Kim · Accepted solution]

Hi,

I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

MVT_INTL (YDAY): =
VAR _yesterdayvalue =
    CALCULATE ( Movements[MVT_INTL(TDAY):], DATEADD ( 'Calendar'[Date], -1, DAY ) )
VAR _startofmonth =
    STARTOFMONTH ( 'Calendar'[Date] ) = MAX ( 'Calendar'[Date] )
RETURN
    IF ( _startofmonth, BLANK (), _yesterdayvalue )

[veasnamuch]

By the way, I am not sure why the sum total also include the value of last day of last month. The display is correct, on 01-Apr MVT_INTL(YDAY) is blank.

[amitchandak]

@veasnamuch , Try like

MVT_INTL (YDAY):=calculate('Movements'[MVT_INTL], DATEADD('Calendar'[Date], -1, DAY) , 'Calendar'[Date] <> eomonth('Calendar'[Date],-1)+1)

or

MVT_INTL (YDAY):=calculate('Movements'[MVT_INTL], DATEADD('Calendar'[Date], -1, DAY) ,filter( 'Calendar', 'Calendar'[Date] <> eomonth('Calendar'[Date],-1)+1))