Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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.
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.
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 , 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.
@amitchandak thank for your suggestion. I tried it but it is for YTD, not last month MTD as I wish 😞
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
100 | |
85 | |
77 | |
66 |
User | Count |
---|---|
120 | |
112 | |
94 | |
84 | |
75 |