Forum Discussion
Previous period
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 # of users
For example, since today date is 2/12/2020 result should be 1000
however if today was 2/29/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
Hi, Anonymous
Based on my research, I created data to reproduce your scenario.
You may create a measure as follows.
Result = var _currentdate = MAX('Table'[date]) var _currentmonthday = DAY(_currentdate) return IF( _currentmonthday<28, LOOKUPVALUE('Table'[# of users],'Table'[date], CALCULATE( MAX('Table'[date]), FILTER( ALLSELECTED('Table'), 'Table'[date]<_currentdate ) ) ), MAX('Table'[# of users]) )Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
9 Replies
Hi, Anonymous
Based on my research, I created data to reproduce your scenario.
You may create a measure as follows.
Result = var _currentdate = MAX('Table'[date]) var _currentmonthday = DAY(_currentdate) return IF( _currentmonthday<28, LOOKUPVALUE('Table'[# of users],'Table'[date], CALCULATE( MAX('Table'[date]), FILTER( ALLSELECTED('Table'), 'Table'[date]<_currentdate ) ) ), MAX('Table'[# of users]) )Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi v-alq-msft,
I am trying to calculate Total Sales %change over time selected by Date slicer.
The following image shows the details of the problem.
On selection of particular date in date slicers, %change shows the Total sales change back to that date selection.
I am trying many options on the internet but all options are shows Year over year %change and that logic is not applicable here.
So please help me out.
Thanks
If you need a previous day, it means if I choose 25 and 24 is not there then I should get null or 0. then prefer to have a date calendar and use it in the calculation. Create the below one as Measure
day behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Day))But if need last day whenever it exists then create a column, 2 step process. Both new columns
Last date = maxx(filter(table,table[date]<earlier(table[Date])),table[Date]) Last # User =maxx(filter(table,table[date]=earlier(table[Last date])),table[# of users])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 Linkedinthank you fo the input.
day behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Day))this is adding up all of the previous dates sales amounts for some reason
I also get this error
EARLIER/EARLIEST refers to an earlier row context which doesn't exist.
when using this code:
Last date = maxx(filter(table,table[date]<earlier(table[Date])),table[Date])
Hello,
You may try this as a calculated column:
Number of Users from Previous Date = //date before current row VAR prevdate = CALCULATE ( MAX ( 'Table'[date] ), FILTER ( 'Table', 'Table'[date] < EARLIER ( 'Table'[date] ) ) ) //date to return depending on number of days VAR prevdate2 = IF ( DAY ( 'Table'[date] ) < 28, prevdate, 'Table'[date] ) RETURN //number of users CALCULATE ( SUM ( 'Table'[# of users] ), FILTER ( 'Table', 'Table'[date] = prevdate2 ) )
