dynamic Calendar Table

[jk_adelaide]

Hi,

I want to create a Calendar table based on the slicer dates( Start date and Enda Date). 

Eg: CalendarDates = CALENDAR([FYStartDate],[FYEndDate]) - (Slicer Dates by SELECTEDVALUE) given in the bottom of the message.

I am creating a report which will show the Financial year as Calendar or Fiscal. I tried the bi-directional solution and it worked based on the selection but showed all the dates in the transaction table. it's not filtering based on the date range. So I thought if my Calendar table only has the slicer date range dates I can overcome this issue, Any idea?

FinStartEnd =

var StartFinYearSelected= SELECTEDVALUE('Switch Calendar'[Year])

var StartFinStartMonth =if(SELECTEDVALUE('FinYearCategory'[FinYearType])= "Calendar",01,07)

var StartDate =DATE(StartFinYearSelected,StartFinStartMonth,01)

var EndFinYearSelected= SELECTEDVALUE('Switch Calendar'[Year])

var EndFinEndMonth =if(SELECTEDVALUE('FinYearCategory'[FinYearType])= "Calendar",12,6)

var EndDate =DATE(if(EndFinEndMonth = 12, EndFinYearSelected,EndFinYearSelected+1),EndFinEndMonth,if(EndFinEndMonth=12,31,30))

RETURN

IF (

SELECTEDVALUE ( 'Financial Year'[Value] ) >= StartDate

&& SELECTEDVALUE ( 'Financial Year'[Value] ) <= EndDate,

1

)

FYEndDate =

var FinYearSelected= SELECTEDVALUE('Switch Calendar'[Year])

var FinEndMonth =if(SELECTEDVALUE('FinYearCategory'[FinYearType])= "Calendar",12,6)

var EndDate =DATE(if(FinEndMonth = 12, FinYearSelected,FinYearSelected+1),FinEndMonth,if(FinEndMonth=12,31,30))

return

EndDate

Thanks in Advance,

JK.

[v-yangliu-msft · Accepted solution]

Hi  @jk_adelaide ,

Calculated tables cannot be affect dinamically by slicers, so you cannot have a dinamic calculted table on the fly to use on a visualization。

You might consider using measure =1 to see the filtered dates on the visual.

Here are the steps you can follow：

1. Create measure.

FYStartDate =
var _StartFinYearSelected=SELECTEDVALUE('Switch Calendar'[Year])
var _StartFinStartMonth=
IF(
    SELECTEDVALUE('FinYearCategory'[FinYearType])="Calendar",1,7)
var _StartDate=DATE(_StartFinYearSelected,_StartFinStartMonth,1)
var _EndFinYearSelected= SELECTEDVALUE('Switch Calendar'[Year])
var _EndFinEndMonth =if(SELECTEDVALUE('FinYearCategory'[FinYearType])= "Calendar",12,6)
var _EndDate =DATE(if(_EndFinEndMonth = 12, _EndFinYearSelected,_EndFinYearSelected+1),_EndFinEndMonth,if(_EndFinEndMonth=12,31,30))
return
IF(
    SELECTEDVALUE ( 'Financial Year'[Value] ) >= _StartDate
&& SELECTEDVALUE ( 'Financial Year'[Value] ) <= _EndDate,
_StartDate
)

FYEndDate =
var FinYearSelected= SELECTEDVALUE('Switch Calendar'[Year])
var FinEndMonth =if(SELECTEDVALUE('FinYearCategory'[FinYearType])= "Calendar",12,6)
var EndDate =DATE(if(FinEndMonth = 12, FinYearSelected,FinYearSelected+1),FinEndMonth,if(FinEndMonth=12,31,30))
return
EndDate

CalendarDates =
IF(
    MAX('Switch Calendar'[Date])>=[FYStartDate]&&[FYEndDate] - SELECTEDVALUE('Financial Year'[Value]),1,0)

2. Place [CalendarDates]in Filters, set is=1, apply filter.

3. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly