Relative Date on Filter starts Sunday (annoyingly), I need it to start Monday

[DRossi]

Hi there, 

Since Power BI does not let you configure the start of week to Monday, instead of Sunday, I need to figure out a formula to do it for me.

I have a Date Table with a relationship to a Jobs table through the Date[Date] to [Jobs]CreatedOn, but I've also inactive relationships between the two for other timestamps such as when the job moves statuses (quote accepted, quote provided etc etc).

I want to create views that displays some key data points for during the current week (Mon-Sun), last week (Mon-Sun), this month, last month etc.

Where do I begin with DAX?

Thanks

[Anonymous]

Hi  @DRossi ,

Sorry, as far as I know, Power Bi's standard week seems to start on Sunday when there is an idea and a poll about this that you can check out:

Microsoft Idea

Relative Dates - Start Monday - Page 2 - Microsoft Fabric Community

Or you can use the slicer of the text and put Measure into the Filter to filter the data in Visual.

Here are the steps you can follow：

1. Enter data – create a table.

2. Create measure.

Flag=
var _today=TODAY()
var _week=WEEKNUM(_today,2)
return
SWITCH(
    TRUE(),
    MAX('Slicer_Table'[Group])="the current week" && WEEKNUM(MAX('Table'[Date]),2)=_week&&YEAR(MAX('Table'[Date]))=YEAR(_today) ,1,
    MAX('Slicer_Table'[Group])="last week"&& WEEKNUM(MAX('Table'[Date]),2)=_week-1&&YEAR(MAX('Table'[Date]))=YEAR(_today),1,
    MAX('Slicer_Table'[Group])="this month"&&MONTH(MAX('Table'[Date]))=MONTH(_today)&&YEAR(MAX('Table'[Date]))=YEAR(_today),1,
    MAX('Slicer_Table'[Group])="last month"&&MONTH(MAX('Table'[Date]))=MONTH(_today)-1&&YEAR(MAX('Table'[Date]))=YEAR(_today),1,0)

3. Place [Flag]in Filters, set is=1, apply filter.

4. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[some_bih]

Hi @DRossi in Power BI you have function in DAX to control first day of week. Check detail about which number mean your need. Hope this help kudos appreviated.

Check link https://dax.guide/weekday/ 

[DRossi]

Thanks. I suppose I then need to figure out how to structure a DAX formula to achieve what i need