Forum Discussion
Helper IVDAX DATEADD WEEK
Bonjour,
j'aimerai créer une formule DAX qui fait la somme des ventes de la semaine dernière uniquement et une 2ème formule qui fait la somme des ventes de la semaine en cours.
J'ai créé la formule ci-dessous, mais elle me permet d'avoir la somme total jusqu'à 7 jours avant ? et non pas la semaine dernière
=CALCULATE([TOTAL Ventes];DATEADD(DATE[Date];-7;DAY))
Merci par avance.
Hi Sylvine_Wyz ,
Based on your description, you want to add last week's sales to this week's sales. The precondition is that you have a continuous index column, which is used as the filtering condition to dynamically get the total sales of the previous week.
So I will transform the data in the query editor and calculate a Year_ Week column, and then group by the Year_ Week column and add index column, finally expand the table and remove unnecessary columns.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Zcy7CQAgDAXAXVIbMInfWcT91/CBjQ/bK24t8exZzdVMklTZ6SEHNaYADaYCKkwVNJna33dQMI2/n/faBw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [date = _t, sales = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"date", type date}, {"sales", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "week", each Date.WeekOfYear([date])), #"Added Conditional Column" = Table.AddColumn(#"Added Custom", "year_week", each if [week] < 10 then Date.Year([date])*1000+[week] else Date.Year([date])*100+[week]), #"Grouped Rows" = Table.Group(#"Added Conditional Column", {"year_week"}, {{"rank", each _, type table [date=nullable date, sales=nullable number, week=number, year_week=number]}}), #"Added Index" = Table.AddIndexColumn(#"Grouped Rows", "Index", 1, 1, Int64.Type), #"Expanded rank" = Table.ExpandTableColumn(#"Added Index", "rank", {"date", "sales", "week", "year_week"}, {"rank.date", "rank.sales", "rank.week", "rank.year_week"}), #"Removed Columns" = Table.RemoveColumns(#"Expanded rank",{"year_week", "rank.week"}) in #"Removed Columns"Then we can create measure like this:
Measure = var current_week = MAX('Table'[Index]) return CALCULATE( SUM('Table'[rank.sales]), FILTER(ALL('Table'), 'Table'[Index]=current_week||'Table'[Index]=current_week-1))Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
- amitchandak
Super User
Sylvine_Wyz , Please refer to my blog and video for WOW
Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123https://www.youtube.com/watch?v=pnAesWxYgJ8
You need new columns like these in date table
Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)
Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)Measures
This Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))
Last year Week= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=(max('Date'[Week Rank]) -52))) - AlB
Community Champion
Hi Sylvine_Wyz
Try
Measure = VAR currentDate_ = MAX ( 'DATE'[Date] ) RETURN CALCULATE ( [TOTAL Ventes]; DATESBETWEEN ( 'DATE'[Date]; currentDate_ - 14; currentDate_ - 8 ) )Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
V-lianl-msftCommunity Support
Hi Sylvine_Wyz ,
Based on your description, you want to add last week's sales to this week's sales. The precondition is that you have a continuous index column, which is used as the filtering condition to dynamically get the total sales of the previous week.
So I will transform the data in the query editor and calculate a Year_ Week column, and then group by the Year_ Week column and add index column, finally expand the table and remove unnecessary columns.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Zcy7CQAgDAXAXVIbMInfWcT91/CBjQ/bK24t8exZzdVMklTZ6SEHNaYADaYCKkwVNJna33dQMI2/n/faBw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [date = _t, sales = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"date", type date}, {"sales", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "week", each Date.WeekOfYear([date])), #"Added Conditional Column" = Table.AddColumn(#"Added Custom", "year_week", each if [week] < 10 then Date.Year([date])*1000+[week] else Date.Year([date])*100+[week]), #"Grouped Rows" = Table.Group(#"Added Conditional Column", {"year_week"}, {{"rank", each _, type table [date=nullable date, sales=nullable number, week=number, year_week=number]}}), #"Added Index" = Table.AddIndexColumn(#"Grouped Rows", "Index", 1, 1, Int64.Type), #"Expanded rank" = Table.ExpandTableColumn(#"Added Index", "rank", {"date", "sales", "week", "year_week"}, {"rank.date", "rank.sales", "rank.week", "rank.year_week"}), #"Removed Columns" = Table.RemoveColumns(#"Expanded rank",{"year_week", "rank.week"}) in #"Removed Columns"Then we can create measure like this:
Measure = var current_week = MAX('Table'[Index]) return CALCULATE( SUM('Table'[rank.sales]), FILTER(ALL('Table'), 'Table'[Index]=current_week||'Table'[Index]=current_week-1))Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
