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 # of users

For example since today date is 2/11/2020 not 2/28/2020 the 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!

[v-frfei-msft · Accepted solution]

Hi @FatBlackCat30 ,

Here we go.

Measure = 
VAR t =
    TODAY ()
VAR day =
    DAY ( t )
VAR pre =
    EDATE ( t, -1 )
VAR inthismonth =
    CALCULATE (
        SUM ( 'Table'[# of users] ),
        FILTER (
            'Table',
            YEAR ( 'Table'[date] ) * 100
                + MONTH ( 'Table'[date] )
                = YEAR ( t ) * 100
                    + MONTH ( t )
        )
    )
VAR premonth =
    CALCULATE (
        SUM ( 'Table'[# of users] ),
        FILTER (
            'Table',
            YEAR ( 'Table'[date] ) * 100
                + MONTH ( 'Table'[date] )
                = YEAR ( pre ) * 100
                    + MONTH ( pre )
        )
    )
RETURN
    IF ( day < 28, premonth, inthismonth )

Pbix as attached.

[amitchandak]

The question is not clear. But in case you are looking for MTD and prior month mtd. Then datesmatd and totalmtd will help. But this does means you need to have a selected date of 11th on the calendar. If it selected as month you need add additional code

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

If month is selected.

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

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
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi

https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

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