Calculate day by day acumulated by week or WTD

[Anonymous]

Hello,

Its been really difficult to find and understand how is calculated a WTD measure.

What i just really want is to have a formular that gives the total of the transactions within the same week, totalizing what you sold from monday to sunday and reset the value to zero at the beginning of each week.

[v-yuta-msft]

@Anonymous ,

To achieve the running total by week, you need a week No. column in the table. If possible, could you please share some sample data for further test?

Regards,

Jimmy Tao

[Anonymous]

Here's the pbi file

https://1drv.ms/u/s!Ag7DnkvMsR95qwP-d0tWSHCdcej0?e=RiplD5

@v-yuta-msft 

Can you help me out plis?

[mwegener]

Gi @Anonymous ,

are you searching for this?

Total Sales WTD =
CALCULATE (
    [Total Sales];
    FILTER (
        ALL ( 'Calendar' );
        'Calendar'[Week] = MAX ( 'Calendar'[Week] )
            && 'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
    )
)

 If I answered your question, please mark my post as solution, this will also help others.

Please give Kudos for support.

[Anonymous]

Hey @mwegener  Thanks a lot, it is almost the result im looking for, something i notice is that it acumulated the amount sold for the previous years the same date, and what i need is to reset the value at the end of every year

So, if i put the measure in a calendar table showing from year to day on the aggregation level,  the 1st week of 2019 value returned is the sum of the sales of the 1st week from 2017 to 2019

[mwegener · Accepted solution]

Hi @Anonymous ,

try this.

Total Sales WTD =
CALCULATE (
    [Total Sales];
    FILTER (
        ALL ( 'Calendar' );
        'Calendar'[Year] = MAX ( 'Calendar'[Year] )
            && 'Calendar'[Week] = MAX ( 'Calendar'[Week] )
            && 'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
    )
)

If I answered your question, please mark my post as solution, this will also help others.

Please give Kudos for support.

[mwegener]

Hi @Anonymous ,

look at this.

https://docs.microsoft.com/en-us/dax/weeknum-function-dax

Date = 
ADDCOLUMNS (
	CALENDARAUTO( ), 
    "Year", YEAR ( [Date] ),
    "Month Name", FORMAT ( [Date], "mmmm" ),
    "Month Number", MONTH ( [Date] ),
    "Weekday", FORMAT ( [Date], "dddd" ),
    "Weekday number", WEEKDAY( [Date] ),
	"Week", WEEKNUM ( [Date], 2),
    "Quarter", "Q" & TRUNC ( ( MONTH ( [Date] ) - 1 ) / 3 ) + 1,
    "Day of Month", Day([Date])
)

If I answered your question, please mark my post as solution, this will also help others.

Please give Kudos for support.

[mwegener]

Hi @Anonymous 

look at this.

https://www.sqlbi.com/articles/week-based-time-intelligence-in-dax/

If I answered your question, please mark my post as solution, this will also help others.

Please give Kudos for support.