Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi.
I am trying to calcuate from my daily sales total a weekly sales total correlating to the week number.
Example would be adding together all 7 entries 0-6 to provide the total for week 13 say in all week 13 entries for that year I have attched a screen shot, I am eventally going to use this data to compare WoW MoM YoY sales. The data runs from 2017 and the formula shown doesnt work for what i require. The week number is repetative so at the turn of teh year starts from 1 again.
Thanks in advance
Solved! Go to Solution.
Hi @Anonymous ,
In my opinion, I'd like to suggest you use year and weeknum and conditions to manually filter calculate record.
Weekly to date total =
CALCULATE (
SUM ( TotalSales[Total] ),
FILTER (
ALL ( TotalSales ),
YEAR ( [Date] ) = EARLIER ( TotalSales[Date] )
&& WEEKNUM ( [Date], 2 ) = WEEKNUM ( EARLIER ( TotalSales[Date] ), 2 )
&& [Date] <= EARLIER ( TotalSales[Date] )
)
)
Regards,
Xiaoxin Sheng
Hi @Anonymous ,
In my opinion, I'd like to suggest you use year and weeknum and conditions to manually filter calculate record.
Weekly to date total =
CALCULATE (
SUM ( TotalSales[Total] ),
FILTER (
ALL ( TotalSales ),
YEAR ( [Date] ) = EARLIER ( TotalSales[Date] )
&& WEEKNUM ( [Date], 2 ) = WEEKNUM ( EARLIER ( TotalSales[Date] ), 2 )
&& [Date] <= EARLIER ( TotalSales[Date] )
)
)
Regards,
Xiaoxin Sheng
Typically, these kind of calculations are done in a measure, but you are trying to do it in a calculated column. That is not a problem per se, it is just worth noting. What is weird though is that you are trying to add this to a calendar table. That just seems odd, unless there is a specific reason for doing so? Could you share a pbix file with you total data structure, (you can make it with dummy data if that helps)?
Proud to be a Super User!
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |