Same period of Last month

[tracytran91]

Hi forks, 

Most of time intelligence function are able to get the full MTD of previous month. However, I would like to get only the Same period of Last month.

For example: Today is Nov 24 2020, I want to get the total sale from Oct 1 2020 to Oct 24 2020.   

 

I wrote the DAX to solve this problem. Unfortunately, it raises the error? 

Could you please help me out regarding to this issue? 

Thank you in advance. 

 

[v-yingjl · Accepted solution]

Hi @tracytran91 ,

If the dataset does not have a continous Calendar date, not recommend to use date-and-time-functions. Try to create measure like this:

Same period of last month =
VAR currrent =
    TODAY ()
VAR mindate =
    DATE ( YEAR ( currrent ), MONTH ( currrent ) - 1, 1 )
VAR maxdate =
    DATE ( YEAR ( currrent ), MONTH ( currrent ) - 1, DAY ( currrent ) )
RETURN
    CALCULATE (
        SUM ( 'Table'[Sales] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Date] >= mindate && 'Table'[Date] <= maxdate )
    )

Attached a sample file in the below, hopes to help you.

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Syndicate_Admin]

Hi, I used the same solution for a problem. However I have trouble filtering by people. Everyone brings me the same amount of data

My model is as follows:

And this is what the VIZ looks like

[tracytran91]

Thank for your help! @v-yingjl 

[amitchandak]

@tracytran91 , Try like examples with a date table

Last MTD =CALCULATE(Sum('order'[Qty]),DATESMTD(dateadd('Date'[Date],-1,year)))

or

Last MTD QTY forced=
var _max = date(year(today()),month(today())-1,day(today()))
return
CALCULATE(Sum('order'[Qty]),DATESMTD(dateadd('Date'[Date],-1,year)),'Date'[Date]<=_max)

// or

//if('Date'[Date]<=_max,CALCULATE(Sum('order'[Qty]),DATESMTD(dateadd('Date'[Date],-1,year))), blank())

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

[tracytran91]

@amitchandak  thank for your suggestion. I tried it but it is for YTD, not last month MTD as I wish 😞