Forum Discussion
Lease End tracker
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.
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
Hi Anonymous @
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-VntwBe5UifxfXbvVtbxa3FRSt-A?e=jSMCU5
Best Regards,
Dedmon Dai
5 Replies
Hi Anonymous @
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-VntwBe5UifxfXbvVtbxa3FRSt-A?e=jSMCU5
Best Regards,
Dedmon Dai
Hi Anonymous ,
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
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