Forum Discussion

logan_logan's avatar
logan_logan
Icon for Helper I rankHelper I
1 year ago
Solved

Weekly growth in trend

Hi, I saw some DAX for weekly growth from daily sales. However my problem is weekly growth is possible in cards, but when it is in trend(bar growph) along the week column (which is text, like "week-...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Thanks for the reply from adudani and NordicDrow.

     

    Hi logan_logan ,

     

     1.Change date to week of year in Power Query Editor:

     

    2.Create simple measures:

    WeeklySales = CALCULATE(SUM([Sales]), ALLEXCEPT('Table', 'Table'[Week of Year],'Table'[Product]))
    WeeklyGrowth = 
VAR CurrentWeekSales =  CALCULATE(SUM([Sales]), ALLEXCEPT('Table', 'Table'[Week of Year],'Table'[Product]))
VAR PreviousWeekSales = CALCULATE('Table'[WeeklySales], 'Table'[Week of Year]= MAX('Table'[Week of Year]) - 1)
RETURN
IF(NOT ISBLANK(PreviousWeekSales), (CurrentWeekSales - PreviousWeekSales) / PreviousWeekSales, BLANK())

     

    3. Sort by week of year:

     

    4. The result is as follows:

     

    I can't reproduce your problem based on my tests, can you provide some more specific descriptions and screenshots as well as the dax formula you're using, it will help the problem to be solved more quickly.

     

    Best Regards,
    Zhu
    Community Support Team

     

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

NordicDrow's avatar
NordicDrow
Icon for Helper I rankHelper I
1 year ago

Hi Logan,

You might want to add some more information, but, I think what you want can be solved in the following manner:

In PowerQuery duplicate your date column. Then right click the new column (must be in date format), select transform -> week -> week of year.

Then if you have a stacked column chart, use week of year in x axis, and sum of sales in y axis.
If you have more than one year of data, you will need to also create a Year column with the same method, and either create a hierarchy Year -> Week, use both columns in x axis or create a year&week column (concatenating both valus, so for example week 17 of 2024 would be 202417).

If you need something more complex, you will probably need to group by in a DAX formula and it usually gets pretty complicated.

Hope this helps!

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      Thanks for the reply from adudani and NordicDrow.

       

      Hi logan_logan ,

       

       1.Change date to week of year in Power Query Editor:

       

      2.Create simple measures:

      WeeklySales = CALCULATE(SUM([Sales]), ALLEXCEPT('Table', 'Table'[Week of Year],'Table'[Product]))
      WeeklyGrowth = 
VAR CurrentWeekSales =  CALCULATE(SUM([Sales]), ALLEXCEPT('Table', 'Table'[Week of Year],'Table'[Product]))
VAR PreviousWeekSales = CALCULATE('Table'[WeeklySales], 'Table'[Week of Year]= MAX('Table'[Week of Year]) - 1)
RETURN
IF(NOT ISBLANK(PreviousWeekSales), (CurrentWeekSales - PreviousWeekSales) / PreviousWeekSales, BLANK())

       

      3. Sort by week of year:

       

      4. The result is as follows:

       

      I can't reproduce your problem based on my tests, can you provide some more specific descriptions and screenshots as well as the dax formula you're using, it will help the problem to be solved more quickly.

       

      Best Regards,
      Zhu
      Community Support Team

       

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