Forum Discussion
Month over month Flag
LastMonth = VAR CD = MAXX ( 'Date', 'Date'[Date] ) VAR LMM = MONTH ( CD ) VAR LMY = YEAR (CD) RETURN IF ( MONTH ( 'Date'[Date] ) = LMM && YEAR ( 'Date'[Date] ) = LMY, 1, 0 )The formula above will mark 1 on the data of latest month available in your table. Till 4th of October (before September data is loaded), the formula above will mark 1 on August data. After the September data is loaded, it will mark 1 on September data.
If you want to go back one month, use EDATE(CD,-1) instead of CD in the above formula.
Similarly, If you want to go back two months, use EDATE(CD,-2) instead of CD in the above formula.
You may use an if condition, if you want to automatically determine if it's -1 or -2 or 0 using a variable.
Based on your requirement, you may modify it.
I modified it slightly
2 MONTHS BACK TRIAL = VAR CD = MAXX('DATE','DATE'[DATE]) VAR AF = IF(MONTH(CD) < MONTH(TODAY()),-1,-2) VAR LMM = MONTH(EDATE(CD,AF)) VAR LMY = YEAR(EDATE(CD,AF)) RETURN IF (MONTH('Date'[DATE])=LMM && YEAR['Date'[Date])=LMY,1,0)You have to subtract one from both 0 and -1 and make it -1 and -2
I will get back to you in sometime. I think the logic needs to be revisited.
Anonymous Okay. IF we add >=4, then it shows 1 for september 4 - 30.
The logic is given that we are on October 2nd: the flag shouls still show one till 31 of August. On 4th of october, flag should show 1 for Sept 1-30.
Similarly in November, from November 1-3, the flag should still be 1 from Sept 1 -30 and on 4th November, it assigns 1 to Oct 1 -31. And so on.
Thank You!
Here is the logic. Please transform to DAX format.
IF DAY(Date[Date])<4 then
----------------------
<< To make the Aug previous month>>
IF(YEAR ('Date'[Date])= YEAR(NOW())
&& MONTH ('Date'[Date] )=MONTH(NOW())- 2,
1,
0
)---------------------
Else
---------------------
<< To make the Sep previous month>>
IF(YEAR ('Date'[Date])= YEAR(NOW())
&& MONTH ('Date'[Date] )=MONTH(NOW())- 1,
1,
0
)--------------------
Thanks
RajHey Anonymous, I am not sure if it is the way I wrote the formula but it's not working.
I guess my IF ELSE is no correct
Hi,
LastMonth = VAR CD = MAXX('Date','Date'[Date]) VAR LMM = MONTH(EDATE(CD,-1)) VAR LMY = YEAR(EDATE(CD,-1)) RETURN IF(MONTH('Date'[Date])=LMM && YEAR('Date'[Date])=LMY,1,0)