Switch/Slicer to toggle between Calendars

[Anonymous]

Hello - 

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 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: 

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

Common Measures:

Sales

YTD Sales Fiscal Year= Calculate(Sum(SalesTable[Sales]),Fiscal YTD =1) ------expected output $30

YTD Sales Reporting Year= Calculate(Sum(SalesTable[Sales]),Reporting YTD =1) ------ expected output $50

Units

YTD Units Fiscal Year= Calculate(Sum(SalesTable[QTY]),Fiscal YTD =1) ------expected output 15

YTD Units Reporting Year= Calculate(Sum(SalesTable[QTY]),Reporting YTD =1) ------ expected output 25

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. 

All the measures are set up with the same format: Caluclate(sum(),calendar ytd =1)

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. 

To further complicate, we have columns for LY Fiscal and LY Reporting, so each of the this year measures  is doubled for a Last Year to Date:

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

so the date filters of the calculations for LY become (Fiscal LYTD=1) and so on. 

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.

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. 

[Stachu]

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]

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]

alternatively you could use flags to identify period, eg.
a - YTD
b - reporting YTD

c - LY YTD

d - reporting LY YTD
date would have a corresponding string e.g.

ab

a

c

cd
so if you would filter YTD, you'd pass 'a' parameter to the query that would create a dynamic flag column

Measure:=
VAR val = SELECTEDVALUE(Slicer[Flag]) --a
VAR temp = FILTER(ADDCOLUMNS(Calendar,"TimeFlag",FIND(val,Calendar[flag],,-1)),[TimeFlag]<>-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

[Anonymous]

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, 'fiscal ytd =1'  and so on. So the switch would modify the measure, would the approach outlined below with  the a, b ,c d facilitate that? 

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? 

I created a test file here:

https://www.dropbox.com/s/n29wek8bxmc0n31/Test.pbix?dl=0

[Anonymous]

were you able to give the test file a look and provide any insights? 

[Stachu]

OK, I get it now
if that's the case I would use calendar like this:

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

then you can use regular DATESYTD for calendar YTD and for reporting YTD use FILTER with appropiate = or <= criteria on RepYear and RepMonth, with LY being RepYear-1

as for the SWITCH - I had in mind something similar to this:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Dynamic-Measures-Titles-Using-SWITCH/td-p/20...