Forum Discussion

Rahul_Bhatt's avatar
Rahul_Bhatt
Icon for Advocate I rankAdvocate I
9 years ago

How to change weekend dynamically country wise

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 ...
Tips and Tricks
Eric_Zhang's avatar
Eric_Zhang
Icon for Microsoft Employee rankMicrosoft Employee
9 years ago

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"
))
Rahul_Bhatt's avatar
Rahul_Bhatt
Icon for Advocate I rankAdvocate I
9 years ago

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