Need help in creating a Weekly column as shown in the pic

[MurugeshC]

Hello All,

I want to create a weekly column (every 7 days) which should show the number of projects handled every week (Monday to Sunday). 

Please check the attached screenshot of the sample fields in excel. I tried using the matrix table with the date field but couldn't get the desired result. 

Your help is highly appreciated. Thanks.

[v-yingjl · Accepted solution]

Hi @MurugeshC ,

Based on your description, you can create this calculated column to create a [weekly beginning] column:

Week beginning =
VAR w =
    WEEKNUM ( 'Table'[Date], 2 )
RETURN
    CALCULATE (
        MIN ( 'Table'[Date] ),
        FILTER ( ALL ( 'Table' ), WEEKNUM ( 'Table'[Date], 2 ) = w )
    )

Create this measure to count values for each week:

Count =
CALCULATE (
    SUM ( 'Table'[Value] ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[Week beginning] IN DISTINCT ( 'Table'[Week beginning] )
    )
)

 

Attached a sample file the below, hopes to help you.

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

[amitchandak]

@MurugeshC , In you date table/table you can create week start like

Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)

or

Week Start date = format('Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1,"DD-MON")

refer Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...

[MurugeshC]

@amitchandak Thank you for your solution, Amit.

I am able to create the weekly column from the Dax formula. Now, I want to sort the columns in ascending order. Please refer the screenshot and requesting you to share your input. I appreciate your help.

Thanks.

[MurugeshC]

Can someone help me with this? Thanks