Forum Discussion
Helper IWeekly 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-1"), then it doesn't work.
Need help
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 TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 Replies
NordicDrowHi 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!
logan_loganafter converting to "week of year" , the column is no longer date format. And my problem strats from there.
I am sharing the file.
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 TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- adudani
Memorable Member
Hi logan_logan ,
kindly provide a sample input and output masking sensitive data.Would have to look at the data. typically a date dimension on a daily granuarilty related to the fact table, along with a dateadd measure provides the result.
- logan_logan
i have shared the test file.
