cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Kurumi0418 Regular Visitor
Regular Visitor

How to use DAX calu weekly list???

 

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.

4 REPLIES 4
Community Support Team
Community Support Team

Re: How to use DAX calu weekly list???

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

1.png2.png

3.png

 

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

 

 

Highlighted
Kurumi0418 Regular Visitor
Regular Visitor

Re: How to use DAX calu weekly list???

@v-juanli-msft

 

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

 

thanks. ^^

Community Support Team
Community Support Team

Re: How to use DAX calu weekly list???

Hi @Kurumi0418

Does this help?

10.png

 

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 Regular Visitor
Regular Visitor

Re: How to use DAX calu weekly list???

@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.

 

2019-02-08 14_32_58-新增 Microsoft PowerPoint 簡報_(Security C).pptx - PowerPoint.png