Forum Discussion
Solved
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...
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)
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)
This worked, thank you!
