Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
My table has 4 fields:
> Year
> Location Name
> Payroll Earnings
> Pay Period (even values of 02 thru 52 - i.e. 02,04,06,08....) These are stored as numeric values and reside in the Legend on the visualization
I have a slicer created for "Pay Period".
I'm attempting to create a measure which will determine the MAX Pay Period value the user has selected in the "Pay Period" Slicer and then return the Payroll Earnings earned in that "Pay Period" only.
I will then utlize that specific measure to create some other comparisons.
Solved! Go to Solution.
You won't be able to do any "time intelligence" without using a date datatype.
You can alter the Filter Context, but I'd advise to not put too much complex logic in your DAX.
You could use the subtract 2, but the problem you're running into is that 2-2 = 0. You want it to map to 52. You will somehow need to manipulate the values so that 02, 2019 = 54 (or some such logic). One possibility, without creating a separate table, is to create a Calculated Column year_period = period + (52 * (year - 2000)). Note: this won't work for years prior to 2000.
Cheers!
Nathan
@Anonymous You are brilliant sir! The data will not go below the year 200 so this this formula seems to be doing the trick where "Year_Period" is a calculated column of: Year_Period = Period + (52 * (Year - 2000):
Hi @dmccormick -
You can do something like:
Max Pay Period Value = var maxPeriod = MAX(<Your table>[Pay Period]) return CALCULATE( [Your measure], <Your table>[Pay Period] = maxPeriod )
Be careful when you reference this measure - the variable assumes that all of the selected pay periods are in scope. If the filter context is a single period, it won't work properly, because the MAX will return the single period in context.
Hope this helps,
Nathan
This is fantastic - thank you very much Nathan @Anonymous !
I'm running into another issue however which I did not previosuly consider which is when the data crosses year or multiple years are selected.
Here is the current command:
@dmccormick - I always use a separate Date dimension table, lke the following. You can open this in Power Query, create a blank query, and paste the script into the Advanced editor.
You can create your logic for pay periods within the date table with a "relative pay period" attribute. So, the pay period which includes today would be 0, the last pay period = -1, etc.
Then you can use the "relative pay period" in your measure.
Date table script:
let //Set the following variables Culture = "English (United States)", //Select a culture. UseYesterdayAsCurrentDate = true, //true = yesterday, false = today YearsBack = 2, //How many years to include prior to the current year. YearsAhead = 2, //How many years to include after the current year. GoToBeginning = "Year", //Options: Year, Month, None GoToEnd = "Year", //Options: Year, Month, None //Figure the Start and End Dates, based on above variables DateToday = DateTime.Date(DateTime.LocalNow()), CurrentDate = if UseYesterdayAsCurrentDate = true then Date.AddDays(DateToday, -1) else DateToday, YearBegin = Date.Year(CurrentDate) - YearsBack, MonthBegin = if GoToBeginning = "Year" then 1 else Date.Month(CurrentDate), DayBegin = if GoToBeginning = "None" then Date.Day(CurrentDate) else 1, StartDate = #date(YearBegin, MonthBegin, DayBegin), YearEnd = Date.Year(CurrentDate) + YearsAhead, MonthEnd = if GoToEnd = "Year" then 12 else Date.Month(CurrentDate), DayEndTemp = if GoToEnd = "Year" then 31 else Date.Day(CurrentDate), EndDateTemp = #date(YearEnd, MonthEnd, DayEndTemp), EndDate = if GoToEnd = "Month" then DateTime.Date(Date.EndOfMonth(EndDateTemp)) else EndDateTemp, DayCount = Duration.Days(Duration.From(EndDate - StartDate)) + 1, //Get complete list of dates, Convert to a table, update name and data type AllDates = List.Dates(StartDate,DayCount,#duration(1,0,0,0)), TableFromList = Table.FromList(AllDates, Splitter.SplitByNothing()), ChangedType = Table.TransformColumnTypes(TableFromList,{{"Column1", type date}}), RenamedColumns = Table.RenameColumns(ChangedType,{{"Column1", "Date"}}), //Add other attributes of the date, as desired InsertYear = Table.AddColumn(RenamedColumns, "Year", each Date.Year([Date])), InsertQuarterOfYear = Table.AddColumn(InsertYear, "Quarter Of Year", each Date.QuarterOfYear([Date])), InsertMonthOfYear = Table.AddColumn(InsertQuarterOfYear, "Month Of Year", each Date.Month([Date])), InsertDayOfMonth = Table.AddColumn(InsertMonthOfYear, "Day Of Month", each Date.Day([Date])), InsertDayOfWeek = Table.AddColumn(InsertDayOfMonth, "Day Of Week", each Date.DayOfWeek([Date])), InsertDateAlternateKey = Table.AddColumn(InsertDayOfWeek, "Date Alternate Key", each [Year] * 10000 + [Month Of Year] * 100 + [Day Of Month]), InsertMonthName = Table.AddColumn(InsertDateAlternateKey, "Month Name", each Date.ToText([Date], "MMMM", Culture), type text), InsertMonthKey = Table.AddColumn(InsertMonthName, "Month Key", each [Year] * 100 + [Month Of Year]), InsertMonthYear = Table.AddColumn(InsertMonthKey, "Month Year", each (try(Text.Range([Month Name],0,3)) otherwise [Month Name]) & " " & Number.ToText([Year])), InsertQuarterName = Table.AddColumn(InsertMonthYear, "Quarter Name", each "Q" & Number.ToText([Quarter Of Year])), InsertQuarterKey = Table.AddColumn(InsertQuarterName, "Quarter Key", each [Year] * 100 + [Quarter Of Year]), InsertQuarterYear = Table.AddColumn(InsertQuarterKey, "Quarter Year", each "Q" & Number.ToText([Quarter Of Year]) & " " & Number.ToText([Year])), InsertDayName = Table.AddColumn(InsertQuarterYear, "Day Of Week Name", each Date.ToText([Date], "dddd", Culture), type text), InsertWeekEnding = Table.AddColumn(InsertDayName, "Week Ending", each Date.EndOfWeek([Date]), type date), //Add Relative Date Positions. InsertRelativeYear = Table.AddColumn(InsertWeekEnding, "Relative Year", each [Year]-Date.Year(CurrentDate)), InsertRelativeYearDescription = Table.AddColumn(InsertRelativeYear, "Relative Year Description", each if [Relative Year] = 0 then "Current Year" else if [Relative Year] = -1 then "Last Year" else if [Relative Year] = 1 then "Next Year" else if [Relative Year] < -1 then Number.ToText(Number.Abs([Relative Year])) & " Years Back" else Number.ToText([Relative Year]) & " Years Ahead"), InsertRelativeQuarter = Table.AddColumn(InsertRelativeYearDescription, "Relative Quarter", each 4*([Year]-Date.Year(CurrentDate)) + ([Quarter Of Year]-Date.QuarterOfYear(CurrentDate))), InsertRelativeQuarterDescription = Table.AddColumn(InsertRelativeQuarter, "Relative Quarter Description", each if [Relative Quarter] = 0 then "Current Quarter" else if [Relative Quarter] = -1 then "Last Quarter" else if [Relative Quarter] = 1 then "Next Quarter" else if [Relative Quarter] < -1 then Number.ToText(Number.Abs([Relative Quarter])) & " Quarters Back" else Number.ToText([Relative Quarter]) & " Quarters Ahead"), InsertRelativeMonth = Table.AddColumn(InsertRelativeQuarterDescription, "Relative Month", each 12*([Year]-Date.Year(CurrentDate)) + ([Month Of Year]-Date.Month(CurrentDate))), InsertRelativeMonthDescription = Table.AddColumn(InsertRelativeMonth, "Relative Month Description", each if [Relative Month] = 0 then "Current Month" else if [Relative Month] = -1 then "Last Month" else if [Relative Month] = 1 then "Next Month" else if [Relative Month] < -1 then Number.ToText(Number.Abs([Relative Month])) & " Months Back" else Number.ToText([Relative Month]) & " Months Ahead"), InsertRelativeWeek = Table.AddColumn(InsertRelativeMonthDescription, "Relative Week", each Duration.Days(Duration.From([Week Ending]-Date.EndOfWeek(CurrentDate)))/7), InsertRelativeWeekDescription = Table.AddColumn(InsertRelativeWeek, "Relative Week Description", each if [Relative Week] = 0 then "Current Week" else if [Relative Week] = -1 then "Last Week" else if [Relative Week] = 1 then "Next Week" else if [Relative Week] < -1 then Number.ToText(Number.Abs([Relative Week])) & " Weeks Back" else Number.ToText([Relative Week]) & " Weeks Ahead"), InsertRelativeDay = Table.AddColumn(InsertRelativeWeekDescription, "Relative Day", each Duration.Days(Duration.From([Date]-CurrentDate))), InsertRelativeDayDescription = Table.AddColumn(InsertRelativeDay, "Relative Day Description", each if [Relative Day] = 0 then "Current Day" else if [Relative Day] = -1 then "Last Day" else if [Relative Day] = 1 then "Next Day" else if [Relative Day] < -1 then Number.ToText(Number.Abs([Relative Day])) & " Days Back" else Number.ToText([Relative Day]) & " Days Ahead"), //Add Date Category Positions. InsertYearGroup = Table.AddColumn(InsertRelativeDayDescription, "Year Group", each if [Relative Year] = 0 then "Current Year" else if [Relative Year] < 0 then "Past Years" else "Future Years"), InsertQuarterGroup = Table.AddColumn(InsertYearGroup, "Quarter Group", each if [Relative Quarter] = 0 then "Current Quarter" else if [Relative Quarter] < 0 then "Past Quarters" else "Future Quarters"), InsertMonthGroup = Table.AddColumn(InsertQuarterGroup, "Month Group", each if [Relative Month] = 0 then "Current Month" else if [Relative Month] < 0 then "Past Months" else "Future Months"), InsertWeekGroup = Table.AddColumn(InsertMonthGroup, "Week Group", each if [Relative Week] = 0 then "Current Week" else if [Relative Week] < 0 then "Past Weeks" else "Future Weeks"), InsertDayGroup = Table.AddColumn(InsertWeekGroup, "Day Group", each if [Relative Day] = 0 then "Current Day" else if [Relative Day] < 0 then "Past Days" else "Future Days"), #"Changed Type" = Table.TransformColumnTypes(InsertDayGroup,{{"Year", Int64.Type}, {"Quarter Of Year", Int64.Type}, {"Month Of Year", Int64.Type}, {"Day Of Month", Int64.Type}, {"Day Of Week", Int64.Type}, {"Date Alternate Key", Int64.Type}, {"Quarter Name", type text}, {"Month Year", type text}, {"Quarter Year", type text}, {"Relative Year Description", type text}, {"Relative Quarter Description", type text}, {"Relative Month Description", type text}, {"Relative Week Description", type text}, {"Relative Day Description", type text}, {"Year Group", type text}, {"Quarter Group", type text}, {"Month Group", type text}, {"Week Group", type text}, {"Day Group", type text}, {"Relative Day", Int64.Type}, {"Relative Week", Int64.Type}, {"Relative Month", Int64.Type}, {"Relative Quarter", Int64.Type}, {"Relative Year", Int64.Type}, {"Quarter Key", Int64.Type}, {"Month Key", Int64.Type}}), //Add Date In Fact Table Attributes. maxSales = Date.AddDays(DateToday, -1), //List.Max(Table.Column(Sales, "SalesDate")), InsertHasSalesData = Table.AddColumn(#"Changed Type", "Has Sales Data", each if [Date] <= maxSales then "Yes" else "No"), InsertHasSalesDataCompleteMonth = Table.AddColumn(InsertHasSalesData, "Has Sales Data Complete Month", each if Date.EndOfMonth([Date]) <= maxSales then "Yes" else "No") in InsertHasSalesDataCompleteMonth
Hope this helps,
Nathan
@Anonymous Would it be possible to accomplish in the DAX statement like something similar to the video below?
https://www.youtube.com/watch?v=jclWnA7pEvY&list=PLV_9sdc9dtKSdOFMZ-04AFiOzmTmQcT32&index=8&t=0s
You won't be able to do any "time intelligence" without using a date datatype.
You can alter the Filter Context, but I'd advise to not put too much complex logic in your DAX.
You could use the subtract 2, but the problem you're running into is that 2-2 = 0. You want it to map to 52. You will somehow need to manipulate the values so that 02, 2019 = 54 (or some such logic). One possibility, without creating a separate table, is to create a Calculated Column year_period = period + (52 * (year - 2000)). Note: this won't work for years prior to 2000.
Cheers!
Nathan
@Anonymous You are brilliant sir! The data will not go below the year 200 so this this formula seems to be doing the trick where "Year_Period" is a calculated column of: Year_Period = Period + (52 * (Year - 2000):