Month over month Flag

[Anonymous]

I have a flag called Last Month which is written as follows :

Last Month = IF(YEAR ('Date'[Date])= YEAR(NOW())
        && MONTH ('Date'[Date] )=MONTH(NOW())- 1,
    1,
    0
)  

The output looks as follows :

So it gives a 1 for all the dates of September as it is the previous month. Similarly when we move to November, the flag would be 1 for all the dates in October. However, my data loads only on the 4th of every month. So on the 1st, 2nd and 3rd of every month there will not be any data for those days since it will still be data as of the previous month (In this case as of Aug 31 for September). On the 4th of october i will have data from 1st September - 30th September.

Because this shows a 1 for the first three days of the month as well, my data changes for all those measures that use this LAst Month Flag. I was wondering how to tweak this formula so that the data is only as of Aug 31, so that when it refreshes on 4th its normal again.  (Basically flag should be 1 as of 31 August till the 4th of October. On 4th of october Sept 1- 30 gets Last month =1. Similarly till 4th of november the flag should be the same and then on the 4th it changes to 1 for Oct 1 - 31 when the data gets refreshed)

Thank You!

[Anonymous · Accepted solution]

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.

[Anonymous · Accepted solution]

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)

[Anonymous]

Hi @Anonymous

Just add DAY(Date[Date])<4 in your condition.

Thanks
Raj

[Anonymous]

Hey @Anonymous,

 This returns as follows :

Since we are on October 1st, I still want the "1" To be for Aug 1 - 31st. On 4th of October, the "1" should be assigned to Sept 1- Sept 30. And it should be one till Nov 3rd until Data refreshes on 4th of November.

[Anonymous]

Sorry, add DAY(Date[Date])>=4.

Thanks

Raj

[Anonymous]

I will get back to you in sometime. I think the logic needs to be revisited.

[Anonymous]

@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!

[Anonymous]

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
Raj

[Anonymous]

Hey @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

[Anonymous]

Hi,

There is a flaw in your formula's logic. You are ensuring that the previous month's year and current month's year are the same. If the current month is January, this will not work and won't give you the correct result. In addition to that, only you know that the data is getting refreshed on 4th of every month. It is better not to rely on your knowledge and let the system determine the refresh status. These two are addressed in the formula suggested by me.

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)

I will try to explain the logic.

We have declared 3 variables. 

CD fetches the latest date of data available in your table. Till the data gets refreshed on 4th or whatever date you are refreshing, the MAXX fetches last month's date. After the data refresh, the MAXX gets the current month's date.

LMM gets the last month's number using EDATE on CD with -1 and 

LMY gets the last month's year number using EDATE on CD with -1. (This will correctly even in case of January)

Using these 3 variables, the last month's dates are flagged as 1 and others as 0.

Hope this clarifies the logic. You may try using this formula by changing the table and field names as per your data model.

[Anonymous]

@Anonymous Hey So i tried this logic. For now the max date in my Date is 8/31/2018. Which means it needs to give a 1 from 8/1 -8/31. But this logic assigns a 1 for July Dates.

If I change your formula to LMM = month(EDATE(CD,-1)) to 0 instead of -1, it gives the right results. But it does not work for year when I tested for Last Date = 1/31/2019. It gives a 1 from 1/1/2018 to 1/31/2018 in that case. I'm guessing it is a small change somewhere, but not able to find where. Thank You!

[Anonymous]

Hi,

Can you try if the following code works or not...

LastMonth =
VAR CD =
    MAXX ( 'Date', 'Date'[Date] )
VAR AF =
    IF ( MONTH ( CD ) < MONTH ( TODAY () ), 0, -1 )
VAR LMM =
    MONTH ( EDATE ( CD, AF ) )
VAR LMY =
    YEAR ( EDATE ( CD, AF ) )
RETURN
    IF ( MONTH ( 'Date'[Date] ) = LMM && YEAR ( 'Date'[Date] ) = LMY, 1, 0 )

[Anonymous]

@Anonymous Yes this worked!

Just a quick question. Would we have to change -1 to -2 if the same logic is applied to 2 months back instead of last month? I kind of tried it but 2 months back shows 1 for August only instead of July.

[Anonymous · Accepted solution]

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.

[Anonymous]

@Anonymous Hey sorry for the repeated posts! But this is what I tried to give me a 1 for July ( when you had given -1, it showd 1 for August, so i put -2 for July).

 

But this also showed a 1 for August only as follows.

[Anonymous · Accepted solution]

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)

[Anonymous · Accepted solution]

You have to subtract one from both 0 and -1 and make it -1 and -2

[Anonymous]

@AnonymousThank You!

[Anonymous]

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)