Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Last Month sales data when year changes shows blank

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

 

Need Help

9 Replies

  • edhans's avatar
    edhans
    Icon for Community Champion rankCommunity Champion
    6 years ago

    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.

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        edhans
        Icon for Community Champion rankCommunity Champion
        6 years ago

        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's avatar
    Anonymous
    Not applicable
    6 years ago

    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