Calculating the total number of sales for that number week.

[Anonymous]

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 

[v-shex-msft · Accepted 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

[JarroVGIT]

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)?