How to use DAX calu weekly list???

[Kurumi0418]

Hi everyone,

How to use DAX Calu weekly list as blow :

1. This week (7days)

2. Last week (7days)

3. Last 2 week (7days)

I want to show trend chart comparise Weekly status.

Thanks.

[v-juanli-msft]

Hi @Kurumi0418

Does this help?

create a date table

date =
ADDCOLUMNS (
    CALENDARAUTO (),
    "year", YEAR ( [Date] ),
    "month", MONTH ( [Date] ),
    "weeknum", WEEKNUM ( [Date], 1 ),
    "weekday", WEEKDAY ( [Date], 2 )
)

connect this date table to your data table

create measures in your data table

this week = IF(YEAR([today])=MAX('date'[year])&&WEEKNUM([today],1)=MAX('date'[weeknum]),1,0)

last week = IF(YEAR([today])=MAX('date'[year])&&WEEKNUM([today],1)=MAX('date'[weeknum])+1,1,0)

last 2 week = IF(YEAR([today])=MAX('date'[year])&&WEEKNUM([today],1)=MAX('date'[weeknum])+2,1,0)

This_week = CALCULATE(SUM(Sheet1[value]),FILTER(Sheet1,[this week]=1))

Last_week = CALCULATE(SUM(Sheet1[value]),FILTER(Sheet1,[last week]=1))

Last2_week = CALCULATE(SUM(Sheet1[value]),FILTER(Sheet1,[last 2 week]=1))

Best Regards

Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Kurumi0418]

@v-juanli-msft

Hello. I'm try it, but it's can't work on my report. 

I want to set weeknum automatic as blow, for now, it's manual, I need manual set it each sunday.

thus, I hope you can teach me a good approach.

Thanks.

[v-juanli-msft]

Hi @Kurumi0418

Today is 2019/1/30

then 

This week (7days)   2019/1/27-2019/2/2

Last week (7days)   2019/1/20-2019/1/26

Last 2 week (7days) 2019/1/13-2019/1/26

Right?

Do you want a visual like a line chart where weekday (monday~sunday) in the "X-axix", values in the "Y-axix", "this week/last week/last 2 weeks" in the Legend field?

Best Regards

Maggie

[Kurumi0418]

@v-juanli-msft

Right, I want this approach. please to share with me.

thanks. ^^