How to change weekend dynamically country wise

[Rahul_Bhatt]

Hello 

I have a dynamic calendar in power bi we created it thru DAX and from date number i am calculating weekend like if day number =6,7 its sat , sun so thats weekend but i have new data where weekend is on Friday & Saturday i want to change this dynamically.

how can i change if Dubai clients looks for data , where weekend is Friday and Sature become weekend if other country cleints look for it where weekend is Sat & sunday then weekend column change accordingly.

calender table has been created thru below dax query :

Calendar  = 

ADDCOLUMNS (
CALENDAR (
DATE ( YEAR ( MIN ( Table[Date] ) ), 1, 1 ),
DATE ( YEAR ( MAX ( Table[Date] ) ), 12, 31 )
),
"Year",INT(FORMAT ( [Date], "YYYY" )),
"Month Number", INT ( FORMAT ( [Date], "MM" ) ),
"Month Name", FORMAT ( [Date], "MMM" ),
"Month Full Name", FORMAT ( [Date], "MMMM" ),
"Quarter", "Q" & INT ( FORMAT ( [Date], "Q" ) ),
"Week Day Number", INT ( WEEKDAY ( [Date],2 ) ),
"Week Day", FORMAT ( [Date], "DDDD" ),
"Year Month ",FORMAT ( [Date], "MMM" ) & " " & YEAR ( [Date] )

)

[Eric_Zhang]

@Rahul_Bhatt

Where would you like to apply the weekend in your report? I'm also curious about how your determine the location of the clients?

My thought for this is to create a country wise calendar and calculated weekend according to the contry. And link those two tables with a normal calendar table.

In the report, use a slicer to filter the country.

country wise Calendar = UNION(
ADDCOLUMNS (
CALENDAR (
DATE ( 2016, 1, 1 ),
DATE ( 2016, 12, 31 )
),
"Year",INT(FORMAT ( [Date], "YYYY" )),
"Month Number", INT ( FORMAT ( [Date], "MM" ) ),
"Month Name", FORMAT ( [Date], "MMM" ),
"Month Full Name", FORMAT ( [Date], "MMMM" ),
"Quarter", "Q" & INT ( FORMAT ( [Date], "Q" ) ),
"Week Day Number", INT ( WEEKDAY ( [Date],2 ) ),
"Week Day", FORMAT ( [Date], "DDDD" ),
"Year Month ",FORMAT ( [Date], "MMM" ) & " " & YEAR ( [Date] ),
"Country","Other"
),
ADDCOLUMNS (
CALENDAR (
DATE ( 2016, 1, 1 ),
DATE ( 2016, 12, 31 )
),
"Year",INT(FORMAT ( [Date], "YYYY" )),
"Month Number", INT ( FORMAT ( [Date], "MM" ) ),
"Month Name", FORMAT ( [Date], "MMM" ),
"Month Full Name", FORMAT ( [Date], "MMMM" ),
"Quarter", "Q" & INT ( FORMAT ( [Date], "Q" ) ),
"Week Day Number", INT ( WEEKDAY ( [Date],2 ) ),
"Week Day", FORMAT ( [Date], "DDDD" ),
"Year Month ",FORMAT ( [Date], "MMM" ) & " " & YEAR ( [Date] ),
"Country","Dubai"
))

[JeroenR]

@Eric_Zhang, I am trying to implement your solution into my dataset. But how do you want to sort by the "Week Day" based on the "Week Day Number" (of the "country wise Calendar" )when these columns are store duplicated, just like the date? When you try to implement this solution, you will get a sorting error by Power BI. 

[Rahul_Bhatt]

I am storing all clients data in one database and every table contain a special column in which property name or code has mentioned so if we filter out the by Property code then we can see the data for that property , 

 To make  the weekend dynamic i have creted a table "Location" in which property , location and weekend information has mentioned like for country A , weekend is "friday &  saturaday ", and for country B weekend is "Saturday and  Sunday".

I set up monday as a first day of week  and in table location we have country , weekend1 & weekend2 column which we mentioed data like that :

Property    Country      Weekend1     Weekend2 

ABC            Dubai          5                         6  

XYZ             India           6                        7

and linked this table with RLS so  only one row will qualify after user loged in. if user1 want to see weekend sale for property ABC  then days filter accordingly and weekend defination mentioned in control table i.e  friday Saturday.

[Day Type] = case when DATEPART(WEEKDAY,table.[ Date]) = Control.weekend1 or DATEPART(WEEKDAY,Control.[Date])= Control.weekend2 then 'WEEKEND'
else 'WEEKDAY' end