Last Month sales data when year changes shows blank

[Anonymous]

HI All,

 I am unable to calculate the last month data when year changes. I tried DATEADD but still it doesn't work for me. 

I have used this measures which is working for my year for 4 months , after 4 months the year changes to 2019 and so this Measures shows blank.

last_2month_sales =

var current_month= MONTH(TODAY())

return CALCULATE(SUM([Sales Revenue]),FILTER('Date_Table',MONTH('Date_Table'[Date])=current_month -2))

Similar formula is used to get the llast_3month_sales, last_4month_sales, last_5month_sales etc..

My model has Many to many with Single cross filter from date table to Sales Table

My output looks like this ( please see attached)

Any help tweaking this measure will be appreaciated.

@amitchandak 

Thanks,

Tejaswi

[v-shex-msft]

HI @Anonymous,

AFAIK, time intelligence functions do not fully works if your not have a calendar or it contains discontinued date range records.

I'd like to suggest you use date function to define filter range which not effect by calendar table:
Time Intelligence "The Hard Way" (TITHW)  
Regards,

Xiaoxin Sheng

[amitchandak]

@Anonymous , try one of the 2. Last 2 month or 2nd last month, that was not very clear.

2nd_month_sales =
var current_month= format(date(year(today()), MONTH(TODAY()-2),year(today())),"YYYYMM")
return
return CALCULATE(SUM([Sales Revenue]),FILTER('Date_Table',format('Date_Table'[Date],"YYYYMM")=current_month -2))

last2_month_sales =
var _max=  Today() //eomonth(today(),-1) //last month end date
var _min=  Today() //eomonth(today(),-3)+1 //start of second last month 
return
return CALCULATE(SUM([Sales Revenue]),FILTER('Date_Table','Date_Table'[Date]>=_min && 'Date_Table'[Date]<=_min ))

You might need to play around with -1, -2 to strat of month

[Anonymous]

Thanks for your reply!

@edhans Attaching sample file for your refernce.

@amitchandak - I will try to use the formula you provided. However, may be my sample file will give you an idea.

I want to extract the data for last_ 5Month_sales ( ie. sales for only that particular month, like i get for last 4 months and other measures)

In my sample file you can see, the last_ 5Month_sales measures gives me output as blank. This is because the dates now changes to 2019 and it is getting confused and hence throw blank.

Thanks,

Tejasw

[edhans]

Hi @Anonymous - a few things - see attached file.

1. I replaced your date table with a real data table that had no gaps. Date tables have to have a contiguous range of dates.
2. I changed the relationship from many to many to one to many from the date table.
3. I marked the date table as a date table in the model
4. I disabled auto date/time in the model.

The below will return sales one month ago based on the date in the file. Then you could put your months and years across the top in a matrix vs a table. See the "Matrix" tab using this.

Sales 1 Month Ago = 
    SUMX(
        FILTER(
            'Sales Table',
            DATEADD('Date Table'[Date],-1,MONTH)
        ),
        'Sales Table'[Sales Revenue]
    )

However, if you want 5 measures to calculate sales for the last 1, 2, 3, 4, and 5 months, you can use this measure - just modify the MonthsAgo variable.

last_5month_sales = 
VAR MonthsAgo = 5
VAR MonthsAdjusted = 
    DATESBETWEEN(
        'Date Table'[Date],
        EOMONTH(TODAY(),-MonthsAgo -1)+1,
        EOMONTH(TODAY(),-MonthsAgo)
    )
VAR SalesPriorMonth =
SUMX(
	FILTER(
		'Sales Table',
		RELATED('Date Table'[Date]) in MonthsAdjusted
		),
		'Sales Table'[Sales Revenue]
		)
RETURN
    SalesPriorMonth

[Anonymous]

HI @edhans ,

Thanks for your reply.

My aplogies, the sample file doesn't show the continuous date. but my actual data is in continuous. so will this still work if i use your code.

The reason I am asking is because I am unable to change the relastionhips in my actual file as those options get grayed out and only Many- Many options shows avaiable for my dataset.

Thanks,

Tejaswi

[edhans]

Then you have duplicate data in your Date file, and it is not contiguous. It needs to be all date from first to last, none missing, none duplicated. You must fix that or date/time intelligence will never work. And there is really no reason other than some sort of error to have a date duplicated in your date table.

[Anonymous]

HI @edhans ,

Thanks for the solution

I found this also as an altenate solution for my issue.

Last 5month Sales- TOTALMTD(Sales_date[Total Sales Revenue],DATEADD(Date_table[Date],-5,MONTH))

Thanks,

Tejaswi

[edhans]

Yes, I didn't think of the MTD function, and for your purpose it should work fine.

I've run into issues where the xxx-To-Date functions don't return "to date" but the full data in a given period. So if you to YearToDate() for this year and you have future dates, it doesn't go through today, it goes through the last date in the current year. So I have gotten in the mindset of DatesBetween where I can control the dates.

[edhans]

You need to use DATEADD. I'd need some data to play with though.

Your measure could work if you handled the year flip. So if this is January, 1 - 2 = -1, so you'd have to convert that to an 11, and then subtract one from the year. 

But this is what date intelligence is for. This article may suggest how to use DATEADD in your model since you have data there to play with and see how it interacts.