Active user through order date

[Sohan]

I want to check if a user is active, by checking if they have had an order in the past 30 days. Right now, I have the following table "Orders"

Invoice date	UserID	Revenue
12-10-2022	1	€100
09-02-2022	2	€140
28-9-2022	3	€90

I want to have the following table:

Date	(UserID) 1	(UserID) 2	(UserID) 3
08-11-2022	true	false	false
09-11-2022	true	false	false
10-11-2022	true	false	false

Does anyone know how to achieve this?

[v-yinliw-msft · Accepted solution]

Hi @Sohan ,

You can try this method:

New columns:

(UserID) 1 = IF(DATEDIFF(CALCULATE(SUM(Orders[Invoice date]), FILTER('Orders','Orders'[UserID] = 1)), 'Table'[Date],DAY) <= 30, TRUE(),FALSE())

(UserID) 2 = IF(DATEDIFF(CALCULATE(SUM(Orders[Invoice date]), FILTER('Orders','Orders'[UserID] = 2)), 'Table'[Date],DAY) <= 30, TRUE(),FALSE())

(UserID) 3 = IF(DATEDIFF(CALCULATE(SUM(Orders[Invoice date]), FILTER('Orders','Orders'[UserID] = 3)), 'Table'[Date],DAY) <= 30, TRUE(),FALSE())

The result is:

Or there are an another method. I agree with the @amitchandak  to create a new table and a new measure, then use matrix visual:

New table:

Table 2 = CALENDAR(TODAY()-3, TODAY())

New measure:

Measure1 =
CALCULATE (
    SUM ( 'Orders'[Revenue] ),
    FILTER (
        'Orders',
        DATEDIFF ( 'Orders'[Invoice date], MAX ( 'Table 2'[Date] ), DAY ) <= 30
    )
)
    <> BLANK ()

And use matrix visual:

Hope this helps you. Here is my PBIX file.

Best Regards,

Community Support Team _Yinliw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Sohan , Create a measure and put the Date on row, User on column, and measure on the value of matrix

measure  =

var _cnt = countrows(Table)

return

If(isblank(_cnt),false(), true())

You can also consider pivot in power query https://radacad.com/pivot-and-unpivot-with-power-bi