Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Weekly time % changes and trends

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

 

Need Help
Show and Tell
Tips and Tricks
  • v-henryk-mstf's avatar
    v-henryk-mstf
    4 years ago

    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.

1 Reply

  • v-henryk-mstf's avatar
    v-henryk-mstf
    Community Support
    4 years ago

    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.