I gotta tell you, I don't understand hindi I can't even recognize a single hindi letter (with all due respect), but I found your video more helpful than hours of searching of an explaination for this kinda tutorial of videos in English, hats off for you my friend. Yes you are that good.
This is extremely helpful brother. I highly Appreciate the help. I was getting lost on how to figure it out and this is the first video that came to me and voila this is it. Thank you again brother!
Hello Sir, Thank you for such a nice tutorial... Everything is explained clearly..... Pl share the date table creation code. That would be really helpful. Thank you once again.
thank you for your reply need your help! I have two tables Active and Resigned and one central date table. I have successfully created visuals for active i.e. headcount by function, grade, region and type but while creating visuals for resigned like year on year leavers I get visuals for all those staff who were not even there in that year. The active table has date of joining since 1960 whereas the resigned table has only three years data 2019, 2020 and 2021 based on the last working date so the power bi should only give my data related to these years only please note that I have calculated total leavers through quick measure based on the last working date and running total on the same pattern and I am using Year (date table) on my axis and running total on value but it gives me the details since 1960 which is from the Active table but there is no last working date which is on the resigned table so the Power BI should only read values from resigned table and bring data for these three years only. Can you please help me fix this?
this is wrong calculation has shown here. the current headcount & Head count number both are same which is not correct, since the Count 476 was including Leavers. Please explain if you can
Sir please adobe Photoshop ki classes upload kardy meny kafe aap ki video dailymotion par dekhy hy par wo kam to ap RUclips par videos upload kardy taky hum behtar to par seekh saki thank you very much
I gotta tell you, I don't understand hindi I can't even recognize a single hindi letter (with all due respect), but I found your video more helpful than hours of searching of an explaination for this kinda tutorial of videos in English, hats off for you my friend. Yes you are that good.
This is extremely helpful brother. I highly Appreciate the help. I was getting lost on how to figure it out and this is the first video that came to me and voila this is it. Thank you again brother!
I'm a bit confused with ISBLANK function used with an OR. Shouldn't OR condition check if either is true and count it?
Hello Sir,
Thank you for such a nice tutorial... Everything is explained clearly.....
Pl share the date table creation code. That would be really helpful.
Thank you once again.
thank you for your reply need your help! I have two tables Active and Resigned and one central date table. I have successfully created visuals for active i.e. headcount by function, grade, region and type but while creating visuals for resigned like year on year leavers I get visuals for all those staff who were not even there in that year. The active table has date of joining since 1960 whereas the resigned table has only three years data 2019, 2020 and 2021 based on the last working date so the power bi should only give my data related to these years only please note that I have calculated total leavers through quick measure based on the last working date and running total on the same pattern and I am using Year (date table) on my axis and running total on value but it gives me the details since 1960 which is from the Active table but there is no last working date which is on the resigned table so the Power BI should only read values from resigned table and bring data for these three years only. Can you please help me fix this?
What is the code in the query?
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, "MonthOfYear", each Date.Month([Date]), type text),
InsertDay = Table.AddColumn(InsertMonth, "DayOfMonth", each Date.Day([Date])),
InsertDayInt = Table.AddColumn(InsertDay, "DateInt", each [Year] * 10000 + [MonthOfYear] * 100 + [DayOfMonth]),
InsertMonthName = Table.AddColumn(InsertDayInt, "MonthName", each Date.ToText([Date], "MMMM"), type text),
InsertCalendarMonth = Table.AddColumn(InsertMonthName, "MonthInCalendar", each (try(Text.Range([MonthName],0,3)) otherwise [MonthName]) & " " & 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, "DayOfWeekName", each Date.ToText([Date], "dddd"), 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 + [MonthOfYear] * 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}, {"MonthOfYear", Int64.Type}, {"QuarterOfYear", Int64.Type}, {"MonthInCalendar", type text}, {"QuarterInCalendar", type text}, {"DayInWeek", Int64.Type}}),
InsertShortYear = Table.AddColumn(ChangedType1, "ShortYear", each Text.End(Text.From([Year]), 2), type text),
AddFY = Table.AddColumn(InsertShortYear, "FY", each "FY"&(if [MonthOfYear]>=FYStartMonth then Text.From(Number.From([ShortYear])+1) else [ShortYear]))
in
AddFY
in
fnDateTable
Hii.. how to calculate attrition Percentage
how to google api Canet to power bi
Can you please share the calendar formula with me?
this is wrong calculation has shown here. the current headcount & Head count number both are same which is not correct, since the Count 476 was including Leavers. Please explain if you can
Sir please explain bank reconciliation statement in ms excel
sir can i have date table code
Hii,
Could you please share the data code of this calculation?
let fnDateTable = (StartDate as date, EndDate as date, optional Culture as nullable text) as table =>
let
DayCount = Duration.Days(Duration.From(EndDate - StartDate))+1,
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),
InsertQuarterNum = Table.AddColumn(InsertYear, "Quarter Num", each Date.QuarterOfYear([Date])),
InsertQuarter = Table.AddColumn(InsertQuarterNum, "Quarter", each "Q" & Number.ToText([Quarter Num])),
InsertMonth = Table.AddColumn(InsertQuarter, "Month Num", each Date.Month([Date]), type text),
InsertStartOfMonth = Table.AddColumn(InsertMonth, "StartOfMonth", each Date.StartOfMonth([Date]), type date),
InsertEndOfMonth = Table.AddColumn(InsertStartOfMonth, "EndOfMonth", each Date.EndOfMonth([Date]), type date),
InsertDay = Table.AddColumn(InsertEndOfMonth, "DayOfMonth", each Date.Day([Date])),
InsertDayInt = Table.AddColumn(InsertDay, "DateInt", each [Year] * 10000 + [Month Num] * 100 + [DayOfMonth]),
InsertMonthName = Table.AddColumn(InsertDayInt, "Month", each Date.ToText([Date], "MMMM", Culture), type text),
InsertShortMonthName = Table.AddColumn(InsertMonthName, "Month short", each Date.ToText([Date], "MMM", Culture), type text),
InsertCalendarMonth = Table.AddColumn(InsertShortMonthName, "Month Year", each [Month short]& " " & Number.ToText([Year]),type text),
InsertCalendarQtr = Table.AddColumn(InsertCalendarMonth, "Quarter Year", each "Q" & Number.ToText([Quarter Num]) & " " & Number.ToText([Year]), type text),
InsertDayWeek = Table.AddColumn(InsertCalendarQtr, "Weekday Num", each Date.DayOfWeek([Date],1)),
InsertDayName = Table.AddColumn(InsertDayWeek, "Weekday", each Date.ToText([Date], "dddd", Culture), type text),
InsertShortDayName = Table.AddColumn(InsertDayName, "Weekday short", each Date.ToText([Date], "ddd", Culture), type text),
InsertWeekEndDate = Table.AddColumn(InsertShortDayName , "EndOfWeek", each Date.EndOfWeek([Date],1), type date),
InsertWeekStartDate = Table.AddColumn(InsertWeekEndDate, "StartOfWeek", each Date.StartOfWeek([Date],1), type date),
InsertWeekNumber= Table.AddColumn(InsertWeekStartDate, "Week Num", each Date.WeekOfYear([Date])),
InsertMonthWeekNumber= Table.AddColumn(InsertWeekNumber, "WeekOfMonth Num", each Date.WeekOfMonth([Date])),
InsertMonthnYear = Table.AddColumn(InsertMonthWeekNumber,"Month-YearOrder", each [Year] * 10000 + [Month Num] * 100),
InsertQuarternYear = Table.AddColumn(InsertMonthnYear,"Quarter-YearOrder", each [Year] * 10000 + [Quarter Num] * 100),
ChangedType1 = Table.TransformColumnTypes(InsertQuarternYear,{{"Quarter-YearOrder", Int64.Type},{"Week Num", Int64.Type},{"WeekOfMonth Num", Int64.Type},{"Quarter", type text},{"Year", type text},{"Month-YearOrder", Int64.Type}, {"DateInt", Int64.Type}, {"DayOfMonth", Int64.Type}, {"Month Num", Int64.Type}, {"Quarter Num", Int64.Type}, {"Weekday Num", Int64.Type}})
in
ChangedType1
in
fnDateTable
سلام سر ڈیسک کے تمام فارمولے پر دوبارہ سے ویڈیو بنائیں
Sir please adobe Photoshop ki classes upload kardy meny kafe aap ki video dailymotion par dekhy hy par wo kam to ap RUclips par videos upload kardy taky hum behtar to par seekh saki thank you very much
google map
sir can i have date table code