Forum Discussion

AT150745's avatar
AT150745
Frequent Visitor
2 years ago
Solved

Help Needed: DAX Formula for Calculating 10-Day Rolling Percentage Change in Power BI

Hello everyone,

I'm working on a Power BI report where I need to calculate the percentage change in sales over a rolling 10-day period. Below is the DAX formula I'm currently using: 

M_TSS_7D_PW% =
VAR __PREV =
CALCULATE(
SUM('Fact Sales Daily 7D'[Gross Sale 7D]),
DATEADD('Dimension Time'[Date], -7, DAY)
)
RETURN
DIVIDE(
SUM('Fact Sales Daily 7D'[Gross Sale 7D]) - __PREV,
__PREV
)

 

To calculate the 10-day rolling percentage change, I use the following formula:

TSS PW 10D =
CALCULATE(
[M_TSS_7D_PW%],
DATESINPERIOD(
'Dimension Time'[Date],
MAX('Dimension Time'[Date]),
-10,
DAY
)
)

The issue I'm facing is that the result starts displaying from the 24th instead of the 31st. I need the calculation to begin from the latest date (31st) in my data.

Here's a screenshot of the current output:

As you can see, the calculation starts on the 24th, but I need it to start on the 31st. Could anyone help me understand why this is happening and how to adjust the formula so that the results start displaying from the 31st?

Thanks in advance for your assistance!

  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi @AT150745,

     

    I was able to use your formula in my testing to show values from the 31st to previous dates.

     

    The columns in the matrix are the formatted calculated columns in /Dimension Time/ table.

     

    TSS PW 10D is a value in the Category column of the Dimension Time table.

    Could you provide the relevant data (pbix files) to facilitate my further testing?


    Best Regards,

    Dengliang Li

     

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

  • AT150745's avatar
    AT150745
    2 years ago

    Hi,

    I need assistance with calculating the percentage change between sales or values for the current week and the previous week in Power BI. I also need to ensure that I can display values for the next 10 days based on a filter.

    Here's what I would like to achieve:

    1. Calculate Percentage Change: I want to calculate the percentage change between sales or values for the current week and the previous week. The calculation should compare values for the same day of each week.

    2. Display Values for Next 10 Days: The report should be able to show values for the next 10 days from the selected date filter.

    Could you please provide guidance or a DAX formula that will help me accomplish this?

    Thank you in advance for your help!

    Best regards,
    Dengliang Li

  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi @AT150745,


    You can use the first DAX formula in your posted question to implement the calculation of the percentage change between the current week's and the previous week's sales or values.

     

    M_TSS_7D_PW% =
VAR __PREV =
CALCULATE(
SUM('Fact Sales Daily 7D'[Gross Sale 7D]),
DATEADD('Dimension Time'[Date], -7, DAY)
)
RETURN
DIVIDE(
SUM('Fact Sales Daily 7D'[Gross Sale 7D]) - __PREV,
__PREV
)

     

     

    You can use the filter's Relative Date to display values for how many days in the future from today.

     

    Best Regards,

    Dengliang Li

     

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



3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi @AT150745,

     

    I was able to use your formula in my testing to show values from the 31st to previous dates.

     

    The columns in the matrix are the formatted calculated columns in /Dimension Time/ table.

     

    TSS PW 10D is a value in the Category column of the Dimension Time table.

    Could you provide the relevant data (pbix files) to facilitate my further testing?


    Best Regards,

    Dengliang Li

     

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

    • AT150745's avatar
      AT150745
      Frequent Visitor
      2 years ago

      Hi,

      I need assistance with calculating the percentage change between sales or values for the current week and the previous week in Power BI. I also need to ensure that I can display values for the next 10 days based on a filter.

      Here's what I would like to achieve:

      1. Calculate Percentage Change: I want to calculate the percentage change between sales or values for the current week and the previous week. The calculation should compare values for the same day of each week.

      2. Display Values for Next 10 Days: The report should be able to show values for the next 10 days from the selected date filter.

      Could you please provide guidance or a DAX formula that will help me accomplish this?

      Thank you in advance for your help!

      Best regards,
      Dengliang Li

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi @AT150745,


        You can use the first DAX formula in your posted question to implement the calculation of the percentage change between the current week's and the previous week's sales or values.

         

        M_TSS_7D_PW% =
VAR __PREV =
CALCULATE(
SUM('Fact Sales Daily 7D'[Gross Sale 7D]),
DATEADD('Dimension Time'[Date], -7, DAY)
)
RETURN
DIVIDE(
SUM('Fact Sales Daily 7D'[Gross Sale 7D]) - __PREV,
__PREV
)

         

         

        You can use the filter's Relative Date to display values for how many days in the future from today.

         

        Best Regards,

        Dengliang Li

         

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