cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper II
Helper II

Same period of Last month

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. 

 

Error PMTD.png

 

 

 

 

1 ACCEPTED SOLUTION
Community Support
Community Support

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 )
    )

last month.png

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.

View solution in original post

4 REPLIES 4
Community Support
Community Support

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 )
    )

last month.png

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.

View solution in original post

Thank for your help! @v-yingjl 

 

Super User IV
Super User IV

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

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

 

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors