Forum Discussion

jk_adelaide's avatar
jk_adelaide
Frequent Visitor
4 years ago
Solved

dynamic Calendar Table

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.

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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