Switch/Slicer to toggle between Calendars

Question

Hello -&nbsp;I have a common sales data source (date, store, sales $, Units and so on) and a dates table as well. We operate using a fiscal calendar that starts 5/1 and would like a 4-5-4/reporting/retail calendar. Common Measures are YTD, LYTD and Year over Year for Sales, margin, units. Instead of using DAX for YTD filters, we added extra columns in the Date Table to indicate if the date is Fiscal YTD or Reporting YTD, typically a comibnation of lookup values and TODAY() with an If statement, so if the date is within&nbsp;YTD then 1 otherwise blank(). I'd like to figure out how to use a slicer so all the YTD and LYTD measures will toggle between fiscal and reporting calendars. Here is sample data:&nbsp;&nbsp;DateFiscal YTDReporting YTD5/1/20181&nbsp;5/2/2018115/3/2018&nbsp;1DateSalesQTY5/1/2018$1055/2/2018$20105/3/2018$3015&nbsp;Common Measures:SalesYTD Sales Fiscal Year= Calculate(Sum(SalesTable[Sales]),Fiscal YTD =1) ------expected output $30YTD Sales Reporting Year= Calculate(Sum(SalesTable[Sales]),Reporting YTD =1) ------ expected output $50&nbsp;UnitsYTD&nbsp;Units Fiscal Year= Calculate(Sum(SalesTable[QTY]),Fiscal YTD =1) ------expected output 15YTD Units Reporting Year= Calculate(Sum(SalesTable[QTY]),Reporting YTD =1) ------ expected output 25&nbsp;&nbsp;I sorted out how to use Switch with a measure but that would mean building the independent table for each of the attibrute measures - Margin, Sales, Units, and so on.&nbsp;&nbsp;All the measures are set up with the same format: Caluclate(sum(),calendar ytd =1)&nbsp;Is there a way to designate a common variable, or parameter or swithc function, so an end user can use a slicer to toggle between the calendar filters of the two calendars.&nbsp;&nbsp;To&nbsp;further complicate, we have columns for LY Fiscal and LY Reporting, so each of the this year measures&nbsp; is doubled for a Last Year to Date:&nbsp;Date FiscalFiscal YTDReporting YTDFiscal LYTDReporting LYTD5/1/20181&nbsp;&nbsp;&nbsp;5/2/201811&nbsp;&nbsp;5/3/2018&nbsp;1&nbsp;&nbsp;5/1/2017&nbsp;&nbsp;1&nbsp;5/2/2017&nbsp;&nbsp;115/3/2017&nbsp;&nbsp;&nbsp;1&nbsp;so the date filters of the calculations for LY become (Fiscal LYTD=1) and so on.&nbsp;&nbsp;One thought, the solution would designate only the 'Fiscal' or 'Reporting' elemend of all the calculations and the slicer would replace that filter value between fiscal and reporting so I create 1 measure per attibute - Sales YTD and Sales LYTD, then depending on the slicer selection the filter (Fiscal YTD =1) would toggle between fiscal and reporting....just a thought.&nbsp;thanks for any help that is out there. Also, I thought of bookmarks and duplicate visuals, but let's be honest, that is a clunky solution.&nbsp;

stachu · Answer

why are you not using the DATESYTD? it would make this much more simple with custom calendar e.g.YTD Sales Reporting Year =
CALCULATE ( SUM ( SalesTable[Sales] ), DATESYTD ( Calendar[Date], "04-31" ) )should correspond to reporting year starting on May 1st, then you can use SAMEPERIODLASTYEAR to move for LY, end use switch for the measures only

anonymous · Answer

I'm not sure I follow. The standard 4-5-4 or Retail calendar has a floating starting date.

More Info Here

So we can't use a designated calendar date for the reporting calendar as it would mitigate the reason for using it.

I'm still not following on how the switch would apply for all the measures. Let's say we have 6 measures: Sales, Sales LY, Units, Units LY, Margin $, Margin $ LY. If those were all in a table for Store 1, Store 2, Store 3 in one summary table, the switch wouldn't work as I am not sure how use switch to refer to the Filter section of the function.

stachu · Answer

OK, I get it nowif that's the case I would use calendar like this:DateRepYearRepMonthRepWeek04/02/201820181105/02/201820181106/02/201820181107/02/201820181108/02/201820181109/02/2018201811............25/02/201820181426/02/201820181427/02/201820181428/02/201820181401/03/201820181402/03/201820181403/03/201820181404/03/201820182505/03/201820182506/03/201820182507/03/201820182508/03/2018201825............04/04/201820182905/04/201820182906/04/201820182907/04/201820182908/04/2018201831009/04/2018201831010/04/2018201831011/04/20182018310then&nbsp;you can use regular DATESYTD for calendar YTD and for reporting YTD use FILTER with appropiate = or &lt;= criteria on RepYear and RepMonth, with LY being RepYear-1as for the&nbsp;SWITCH -&nbsp;I had in mind something similar to this:https://community.powerbi.com/t5/Quick-Measures-Gallery/Dynamic-Measures-Titles-Using-SWITCH/td-p/200718

stachu · Answer

alternatively you could use flags to identify period, eg.a - YTDb - reporting YTDc - LY YTDd - reporting LY YTDdate would have a corresponding string e.g.abaccdso if you would filter YTD, you'd pass 'a' parameter to the query that would create a dynamic flag column&nbsp;Measure:=VAR val = SELECTEDVALUE(Slicer[Flag]) --a
VAR temp = FILTER(ADDCOLUMNS(Calendar,"TimeFlag",FIND(val,Calendar[flag],,-1)),[TimeFlag]&lt;&gt;-1) --filters all the rows that have 'a' in the flag column
RETURN
CALCULATE([Sales],temp)but I expect performance to be an issue with this approach&nbsp;

anonymous · Answer

If I follow the first approach, that would work for one visualization, but the switch is needed across the reporting suite and on each page ideally it would apply to all the visuals. We might have a bar chart for YTD vs. LYTD Sales, then a matrix with sold units YTD vs. LYTD and a guage for Margin performance and so on. I'm looking for a solution that will apply to all of the visuals that incorporate a measure that has a filter like,&nbsp;'fiscal ytd =1'&nbsp; and so on. So the switch would modify the measure, would the approach outlined below with&nbsp; the a, b ,c d facilitate that?&nbsp;&nbsp;Would we have 1 column with concatenated market flags. So 'a' would be YTD, 'b' would be LYTD, 'c' would be Reporting YTD, 'd' would be Reporting LYTD, so on each day there could be an 'a' or 'ab' or 'c' or 'cd' and so on. That would allow us to refer to a single filtered column instead of referencing 4 different columns? We could be more literal and have 'reportingfiscal ytd' and 'reportingytd' and 'fiscal lytd' and 'reportingfiscal lytd' as indicators in 1 column. Then the slicer could point to a 'contains' function. So as you switch the slicer it would point to a contains 'reporting' or 'fiscal' in the measure and then it would apply to all the measures with that filter. Does that make sense, how would that get set up?&nbsp;&nbsp;I created a test file here:https://www.dropbox.com/s/n29wek8bxmc0n31/Test.pbix?dl=0&nbsp;

Forum Discussion

Switch/Slicer to toggle between Calendars

6 Replies

Related content

Dynamic RLS + Slicer Role Switching Issue

Switching visuals by slicer

Calendar Slicer with Different Business Calendars

Custom calendar

Tweaking Financial Calendar

Date	Fiscal YTD	Reporting YTD
5/1/2018	1
5/2/2018	1	1
5/3/2018		1

Date	Sales	QTY
5/1/2018	$10	5
5/2/2018	$20	10
5/3/2018	$30	15

Date Fiscal	Fiscal YTD	Reporting YTD	Fiscal LYTD	Reporting LYTD
5/1/2018	1
5/2/2018	1	1
5/3/2018		1
5/1/2017			1
5/2/2017			1	1
5/3/2017				1

Date	RepYear	RepMonth	RepWeek
04/02/2018	2018	1	1
05/02/2018	2018	1	1
06/02/2018	2018	1	1
07/02/2018	2018	1	1
08/02/2018	2018	1	1
09/02/2018	2018	1	1
...	...	...	...
25/02/2018	2018	1	4
26/02/2018	2018	1	4
27/02/2018	2018	1	4
28/02/2018	2018	1	4
01/03/2018	2018	1	4
02/03/2018	2018	1	4
03/03/2018	2018	1	4
04/03/2018	2018	2	5
05/03/2018	2018	2	5
06/03/2018	2018	2	5
07/03/2018	2018	2	5
08/03/2018	2018	2	5
...	...	...	...
04/04/2018	2018	2	9
05/04/2018	2018	2	9
06/04/2018	2018	2	9
07/04/2018	2018	2	9
08/04/2018	2018	3	10
09/04/2018	2018	3	10
10/04/2018	2018	3	10
11/04/2018	2018	3	10