Forum Discussion
Switch Between Different Holiday Calendars
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!
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 column3rd 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 measuresOutput: (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)
2 Replies
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 column3rd 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 measuresOutput: (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)
This worked, thank you!
