Switch Between Different Holiday Calendars

[amaronda]

Hello,

I am working on a report where I need to be able to change all visualizations based on the location. I have 2 locations- United States and Puerto Rico. I created 2 date tables because each location celebrates different holidays. The "Working Day" column uses a 1 to designate a work day and a 0 to desginate a non-working day (due to holidays or weekends). 

Here is the link to the data I am using.

I want to make a table showing the following information for the week of November 25-December 1. The Sales Per Work Day column takes the Working Day column into account, where United States has 3 work days during this time period while Puerto Rico has 4 work days.

How can I make a Date Table to account for different Work Days in 2 different locations? I would also like to be able to use the locations in a slicer, so if I were to create a graph with the data from the table above I could simply use a slicer to switch between Locations (without setting up a bookmark). Would I need to set up a Location lookup table?

As of now I only have 2 locations, but there will be 10+ in the future so ideally the solution would have minimal hard-coding to account for locations. Each future location will have their own Holiday calendar, so Location names would be derived from there.

Thank you!

[Bmejia · Accepted solution]

1st, create a date(calendar table with just date column) I used the minimum date(year) and max date(year) on the sales table.

Date =

    CALENDAR( DATE( YEAR( MIN( 'Sales Data'[Date] ) ), 1, 1 ), DATE( YEAR( MAX( 'Sales Data'[Date] ) ), 12, 31 ) )

2nd join all the tables on the Date column

3rd Create two measures as follow to sum the total sales and per work day sales

Total Sales =

VAR PRSales = CALCULATE(SUM('Sales Data'[Sales]),'Sales Data'[Location]="Puerto Rico")

VAR USSales = CALCULATE(SUM('Sales Data'[Sales]),'Sales Data'[Location]="United States")

VAR location = SELECTEDVALUE('Sales Data'[Location])

RETURN

SWITCH(TRUE(),

location="Puerto Rico",PRSales,

location="United States",USSales,

BLANK())

Sales Per Work Day =

VAR PRSales = CALCULATE(SUM('Sales Data'[Sales]),'Sales Data'[Location]="Puerto Rico",'Puerto Rico Holidays'[Working Day]=1)

VAR USSales = CALCULATE(SUM('Sales Data'[Sales]),'Sales Data'[Location]="United States",'US Holidays'[Working Day]=1)

VAR location = SELECTEDVALUE('Sales Data'[Location])

RETURN

SWITCH(TRUE(),

location="Puerto Rico",PRSales,

location="United States",USSales,

BLANK())

4th Create your table drop the location from the sales table and your measures

Output: (note per the sales per work day got different values, I am not sure if you were looking at differet values, but base on what you indicated the values below seem correct)

[amaronda]

This worked, thank you!