Weekly time % changes and trends

[Anonymous]

Hi there, 

I have a table with count of new users by day. The date column is connected to a calendar table, where I've included a 'week' and 'week commencing' column. I've set the week commencing to be Sunday

Is someone about to help me with some DAX for: 

Current week: Sum of users in current week (whenever report is refreshed) 

Last full week: sum of users in last full week (Sun - Sat) 

% change against week prior 

% change against same week last year

% change against weekly average over last 12 months

Thank you so much in advance!

Sonia

[v-henryk-mstf · Accepted solution]

Hi @Anonymous ,

Try formula like below:

weeknum =
CALCULATE (
    SUM ( 'Table 2'[value ),
    FILTER (
        ALL ( 'Table 2' ),
        YEAR ( 'Table 2'[Date] ) = YEAR ( TODAY () )
            && 'Table 2'[Week] = WEEKNUM ( TODAY (), 1 )
    )
)

Similar to the ringgit, the growth over the same period can be referred to the following formula:

last week = CALCULATE([Amount_filter],DATEADD(M_DimDate[Date],-1,Week))

last week_amount =
VAR Pervious = [last week]
VAR diff =
    SUM ( FactInternetSales[SalesAmount] ) - Pervious
RETURN
    DIVIDE ( diff, Pervious, BLANK () )

For more details, you can read below blog:

Calculate Percentage Growth Over Time with Power BI (mssqltips.com)

Rolling 12 Months Average in DAX - SQLBI

If the problem is still not resolved, please provide detailed error information and test data. Looking forward to your reply.

Best Regards,
Henry

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