returning the previous periods total

[FatBlackCat30]

Hello all,

I am trying to create a DAX formula following this logic:

if the current date is less then 28 days then return the previous dates value under the column called users

For example since today date is 2/11/2020 not 2/28/2020 then the result should be 1000 however if today was 2/28/2020 then the result would be 100

date	# of users
2/11/2020	100
1/31/2020	1000
12/31/2019	1024
11/30/2019	1556
10/31/2019	1257
9/30/2019	8855
8/31/2019	5745
7/31/2019	25255
6/30/2019	154
5/31/2019	154
4/30/2019	5455
3/31/2019	8888
2/28/2019	2665

what would be the best approach to do this?

thank you!

[amitchandak]

If you looking to control by date, then date should be in the filter. In case the month is in the filter you need to add a filter for the date. Do you need rolling formula ?

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
last year MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-12,MONTH))))

Date filter

last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)),day('Date'[Date])<=day(Today()))

Rolling

Rolling 30 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],Max(Sales[Sales Date]),-30,Day))

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Connect on Linkedin

[parry2k]

@FatBlackCat30 can you share sample data and expected output. Read this post to get your answer quickly.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490