Lease End tracker

[Amardeep100115]

Hi Super Users,

I need help on creating calenda or any other  visual which will show the lease end event in power BI with remaining number of days remaining. 

here i am sharing some samle data and the below DAX which i am using for getting count of number of event.

Lease expiry = SWITCH (

TRUE,

SELECTEDVALUE ( 'Date Slicer'[Month] ) = BLANK ()

&& SELECTEDVALUE ( 'Date Slicer'[Fiscal Year] ) <> BLANK (), CALCULATE (

Sum ( 'Lease expr'[Count]),

FILTER (

'Lease expr',

MONTH ( 'Lease expr'[Month&Year] )

= MONTH ( TODAY () )

&& 'Lease expr'[Year]

= CALCULATE (

VALUE ( ( MAX ( 'Date Slicer'[Year] ) ) ),

FILTER (

'Date Slicer',

'Date Slicer'[Fiscal Year] = SELECTEDVALUE ( 'Date Slicer'[Fiscal Year] )

)

)

)

),

SELECTEDVALUE ( 'Date Slicer'[Month] ) = BLANK ()

&& SELECTEDVALUE ( 'Date Slicer'[Fiscal Year] ) = BLANK (), CALCULATE (

Sum ( 'Lease expr'[Count] ),

FILTER (

'Lease expr',

MONTH ('Lease expr'[Month&Year] )

= MONTH ( TODAY () )

&&'Lease expr'[Year] = YEAR ( TODAY () )

)

),

SELECTEDVALUE ( 'Date Slicer'[Month] ) <> BLANK ()

&& SELECTEDVALUE ( 'Date Slicer'[Fiscal Year] ) <> BLANK (), CALCULATE (

Sum ( 'Lease expr'[Count]),

FILTER (

'Lease expr',

MONTH ( 'Lease expr'[Month&Year] )

= CALCULATE (

MONTH ( MAX ( 'Date Slicer'[Date] ) ),

FILTER (

'Date Slicer',

'Date Slicer'[Month] = SELECTEDVALUE ( 'Date Slicer'[Month] )

)

)

&& 'Lease expr'[Year]

= CALCULATE (

VALUE ( MAX ( 'Date Slicer'[Year] ) ),

FILTER (

'Date Slicer',

'Date Slicer'[Fiscal Year] = SELECTEDVALUE ( 'Date Slicer'[Fiscal Year] )

)

)

)

)

)

Sample Data  

Above DAX is used for historical data where i have used for past lease count as well. Also the visual should change on selection of month as well

Fiscal Calendar ( i used in my file) (date: 01/01/2016 to 12/31/2028)

let fnDateTable = (StartDate as date, EndDate as date, FYStartMonth as number) as table =>
let
DayCount = Duration.Days(Duration.From(EndDate - StartDate)),
Source = List.Dates(StartDate,DayCount,#duration(1,0,0,0)),
TableFromList = Table.FromList(Source, Splitter.SplitByNothing()),
ChangedType = Table.TransformColumnTypes(TableFromList,{{"Column1", type date}}),
RenamedColumns = Table.RenameColumns(ChangedType,{{"Column1", "Date"}}),
InsertYear = Table.AddColumn(RenamedColumns, "Year", each Date.Year([Date]),type text),
InsertYearNumber = Table.AddColumn(RenamedColumns, "YearNumber", each Date.Year([Date])),
InsertQuarter = Table.AddColumn(InsertYear, "QuarterOfYear", each Date.QuarterOfYear([Date])),
InsertMonth = Table.AddColumn(InsertQuarter, "Month Number", each Date.Month([Date]), type text),
InsertDay = Table.AddColumn(InsertMonth, "DayOfMonth", each Date.Day([Date])),
InsertDayInt = Table.AddColumn(InsertDay, "DateInt", each [Year] * 10000 + [Month Number] * 100 + [DayOfMonth]),
InsertMonthName = Table.AddColumn(InsertDayInt, "Month", each Date.ToText([Date], "MMM"), type text),
InsertCalendarMonth = Table.AddColumn(InsertMonthName, "Month&Year", each (try(Text.Range([Month],0,3)) otherwise [Month]) & " " & Number.ToText([Year])),
InsertCalendarQtr = Table.AddColumn(InsertCalendarMonth, "QuarterInCalendar", each "Q" & Number.ToText([QuarterOfYear]) & " " & Number.ToText([Year])),
InsertDayWeek = Table.AddColumn(InsertCalendarQtr, "DayInWeek", each Date.DayOfWeek([Date])),
InsertDayName = Table.AddColumn(InsertDayWeek, "Day", each Date.ToText([Date], "ddd"), type text),
InsertWeekEnding = Table.AddColumn(InsertDayName, "WeekEnding", each Date.EndOfWeek([Date]), type date),
InsertWeekNumber= Table.AddColumn(InsertWeekEnding, "Week Number", each Date.WeekOfYear([Date])),
InsertMonthnYear = Table.AddColumn(InsertWeekNumber,"MonthnYear", each [Year] * 10000 + [Month Number] * 100),
InsertQuarternYear = Table.AddColumn(InsertMonthnYear,"QuarternYear", each [Year] * 10000 + [QuarterOfYear] * 100),
ChangedType1 = Table.TransformColumnTypes(InsertQuarternYear,{{"QuarternYear", Int64.Type},{"Week Number", Int64.Type},{"Year", type text},{"MonthnYear", Int64.Type}, {"DateInt", Int64.Type}, {"DayOfMonth", Int64.Type}, {"Month Number", Int64.Type}, {"QuarterOfYear", Int64.Type}, {"Month&Year", type text}, {"QuarterInCalendar", type text}, {"DayInWeek", Int64.Type}}),
InsertShortYear = Table.AddColumn(ChangedType1, "ShortYear", each Text.End(Text.From([Year]), 4), type text),
AddFY = Table.AddColumn(InsertShortYear, "Fiscal Year", each "FY"&" "&(if [Month Number]>=FYStartMonth then Text.From(Number.From([ShortYear])+1) else [ShortYear]))
in
AddFY
in
fnDateTable

[Amardeep100115]

Could you please add calender visual and guide me? 

Please

[v-deddai1-msft]

Hi  @Amardeep100115 ,

Have you check the pbix file I shared above? What did you mean the calender visual and Would you please show us more detailed information(such as your expected output)?

Best Regards,

Dedmon Dai

[Amardeep100115]

Hi @v-deddai1-msft 

Sorry to delay in replying, i wish to show upcoming lease event which may be in next 30 days, 60 days, 90days .....etc

and when i select month from slicer it should show lease data by that month, the measure which i give in first message, i am using that for all results

if i am not clear please let me know

[v-deddai1-msft · Accepted solution]

Hi @Amardeep100115 @

According to your description, I suggest you create calculated table 'FY' from your calendar table and calculated table 'Month' from  calendar table for Fiscal Year slicer and month slicer:

FY = VALUES('Invoked Function'[FY]) //Since the fiscal year in your sample is a text type, a calculated column FY is created to calculate the fiscal year.



Month = VALUES('Invoked Function'[Month Number])

Finally change your Lease expiry to the following dax code:

Lease expiry =

SWITCH (

    TRUE,

    ISFILTERED ( 'Month'[Month Number] ) = FALSE ()

        && ISFILTERED ( FY[FY] ), CALCULATE (

        SUM ( 'Lease expr'[Count] ),

        FILTER (

            'Lease expr',

            MONTH ( 'Lease expr'[Month&Year] ) = MONTH ( TODAY () )

                && 'Lease expr'[Year] = SELECTEDVALUE ( FY[FY] )

        )

    ),

    ISFILTERED ( 'Month'[Month Number] ) = FALSE ()

        && ISFILTERED ( FY[FY] ) = FALSE (), CALCULATE (

        SUM ( 'Lease expr'[Count] ),

        FILTER (

            'Lease expr',

            MONTH ( 'Lease expr'[Month&Year] ) = MONTH ( TODAY () )

                && 'Lease expr'[Year] = YEAR ( TODAY () )

        )

    ),

    ISFILTERED ( 'Month'[Month Number] ) && ISFILTERED ( FY[FY] ), CALCULATE (

        SUM ( 'Lease expr'[Count] ),

        FILTER (

            'Lease expr',

            MONTH ( 'Lease expr'[Month&Year] ) = SELECTEDVALUE ( 'Month'[Month Number] )

                && 'Lease expr'[Year] = SELECTEDVALUE ( FY[FY] )

        )

    )

)

Please refer to the pbix file: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/Ea4lx17lmONHqgR4Xs...

Best Regards,

Dedmon Dai

[Amardeep100115]

Could you please reshare this file