Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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 )
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
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 )
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
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.
@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))
User | Count |
---|---|
97 | |
87 | |
77 | |
67 | |
63 |
User | Count |
---|---|
111 | |
96 | |
96 | |
67 | |
63 |