Forum Discussion
Count rows based on a filter/date
Hi Anonymous ,
First, you need to relate your calendar table to your fact table.
Drag [Date] from calendar to [occurred] - this should apply an ACTIVE relationship.
Then drag [Date] from calendar to [escalated to design] - this should apply an INACTIVE relationship.
Both relationships should show as calendar is the ONE side and Service Request Table is the MANY side.
Then set up these measures:
_requestsOccurred = DISTINCTCOUNT('Service request table'[Service Request ID]) _requestsEscalated = CALCULATE( DISTINCTCOUNT('Service request table'[Service Request ID]), USERELATIONSHIP('Service request table'[escalated to design], Calendar[Date]) )Use the Calendar table date/week number as your visual axis.
You should now be able to use both of these measures in the same visual and view their values within a common timeframe.
Pete
Pete
Pete,
all working now. I had to revert the fact table data fields to the start of the day to match my calendar.
Thanks so much for your pointing me to the direction. I was not able to understand how to use relationship in DAX and your simple example helped me to understand it. I'm already planning to use this for a backlog calculation (created vs closed scoring).
Cheers,
Jiri
Hi Anonymous ,
Glad it's all working.
Just some pointers on best practice:
- ALWAYS include a proper Calendar table in your report models, relate this to your fact tables, and use Calendar values as visual axes.
- Turn off Time intelligence here (and would also recommend turning off Autodetect relationships):
- Mark your calendar table as a date table here:
There are a number of reasons why this practice is best-practice:
1) The hierarchies that you said got broken are actually all individual date tables in the background. If your model has a wide range of dates this can make the data size of your model very big very quickly.
2) A lot of (maybe all) time intelligence functions e.g. SAMEPERIODLASTYEAR, DATESYTD etc. do not work unless implemented using proper date table dates as arguments.
3) Using a proper calendar table, you can highly customise your own hierarchies and time periods, and not be restricted to just Year, Quarter, Month.
Here is an example of the calendar table I use in all my models. The Financial fields might not work for you (they run 1st April to 31st March), but can be edited easily enough to suit your company financial year. In Power Query, go to New Source>Blank Query then in Advanced Editor paste my code over the default code. You will then have a fully-complete calendar to use going forward if you want:
let
// Declare acctChgDay variable
acctChgDay = 10,
// Define Date.Today
Date.Today = Date.From(DateTime.LocalNow()),
Source = { Number.From(#date(Date.Year(Date.AddYears(Date.Today+#duration(275,0,0,0),-3)),4,1))..Number.From(#date(Date.Year(Date.AddYears
(Date.Today+#duration(275,0,0,0),0)),3,31)) },
convToTable = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
chgDateType = Table.TransformColumnTypes(convToTable, {{"Column1", type date}}),
renCols = Table.RenameColumns(chgDateType, {{"Column1", "date"}}),
addDateKey = Table.AddColumn(renCols, "dateKey", each Date.ToText([date], "yyyMMdd"), type text),
addFinYear = Table.AddColumn(addDateKey, "finYear", each Date.Year([date]+#duration(275,0,0,0))),
addRelativeFY = Table.AddColumn(addFinYear, "relativeFY", each [finYear] - Date.Year(Date.Today+#duration(275,0,0,0))),
addFinPeriod = Table.AddColumn(addRelativeFY, "finPeriod", each if Date.Month([date]) >=4 then Date.Month([date])-3 else Date.Month([date])+9),
addFinYearPeriod = Table.AddColumn(addFinPeriod, "finYearPeriod", each [finYear]*100+[finPeriod]),
addFinWeekKey = Table.AddColumn(addFinYearPeriod, "finWeekKey", each Date.WeekOfYear(Date.AddWeeks([date],-13),Day.Monday)),
addFinHY = Table.AddColumn(addFinWeekKey, "finHY", each if [finPeriod] >= 1 and [finPeriod] <= 6 then "H1" else "H2"),
addFinQtr = Table.AddColumn(addFinHY, "finQtr", each if [finPeriod] >= 1 and [finPeriod] <= 3 then "Q1"
else if [finPeriod] >= 4 and [finPeriod] <= 6 then "Q2"
else if [finPeriod] >= 7 and [finPeriod] <= 9 then "Q3"
else "Q4"),
addMonthKey = Table.AddColumn(addFinQtr, "monthKey", each Date.Month([date])),
addMonth = Table.AddColumn(addMonthKey, "month", each Text.Start(Date.MonthName([date]),3)),
addMonthYear = Table.AddColumn(addMonth, "monthYear", each Text.Combine({[month], Text.End(Text.From(Date.Year
([date])),2)}, " ")),
addCurrentMonth = Table.AddColumn(addMonthYear, "currentMonth", each if Date.Month(Date.Today) = Date.Month([date]) and Date.Year(Date.Today) = Date.Year([date]) then "CurrentMonth"
else if [date] < Date.StartOfMonth(Date.Today) then "History" else "Future"),
addRelativeMonth = Table.AddColumn(addCurrentMonth, "relativeMonth", each (Date.Year([date]) * 12 + [monthKey]) - (Date.Year(Date.Today) * 12 + Date.Month(Date.Today))),
addDayKey = Table.AddColumn(addRelativeMonth, "dayKey", each Date.DayOfWeek([date])),
addDay = Table.AddColumn(addDayKey, "day", each Text.Start(Date.DayOfWeekName([date]), 3)),
addCurrentDay = Table.AddColumn(addDay, "currentDay", each if Date.Today = [date] then "CurrentDay"
else if [date] < Date.Today then "History" else "Future"),
addDayType = Table.AddColumn(addCurrentDay, "dayType", each if [day] = "Sat" or [day] = "Sun" then "Weekend" else "Weekday"),
addDayView = Table.AddColumn(addDayType, "dayView", each if [dayType] = "Weekend"
then null
else if [date] = Date.AddDays(Date.Today, -7)
then "OneWeekAgo"
else if [day] = "Fri" and [date] = Date.AddDays(Date.Today, -3)
then "LastWorkDay"
else if [date] = Date.AddDays(Date.Today, -1)
then "LastWorkDay"
else if [date] = Date.Today
then "Today"
else if [day] = "Mon" and [date] = Date.AddDays(Date.Today, 5)
then "NextWorkDay+3"
else if [day] = "Mon" and [date] = Date.AddDays(Date.Today, 4)
then "NextWorkDay+2"
else if [day] = "Mon" and [date] = Date.AddDays(Date.Today, 3)
then "NextWorkDay+1"
else if [day] = "Tue" and [date] = Date.AddDays(Date.Today, 5)
then "NextWorkDay+3"
else if [day] = "Tue" and [date] = Date.AddDays(Date.Today, 4)
then "NextWorkDay+2"
else if [day] = "Wed" and [date] = Date.AddDays(Date.Today, 5)
then "NextWorkDay+3"
else if [date] = Date.AddDays(Date.Today, 1)
then "NextWorkDay+1"
else if [date] = Date.AddDays(Date.Today, 2)
then "NextWorkDay+2"
else if [date] = Date.AddDays(Date.Today, 3)
then "NextWorkDay+3"
else null),
addCurrentPdAccts = Table.AddColumn(addDayView, "currentPdAccts", each if
(Date.IsInPreviousMonth([date]) and Date.Day(Date.Today) < acctChgDay)
or
(Date.Month([date]) = Date.Month(Date.Today) and Date.Year(Date.Today) = Date.Year([date]) and Date.Day(Date.Today) < acctChgDay)
then "Current Pd"
else
if
(Date.Month([date]) = Date.Month(Date.Today) and Date.Year(Date.Today) = Date.Year([date]) and Date.Day(Date.Today) >= acctChgDay)
then "Current Pd"
else
if
[date] < Date.Today
then "History"
else "Future"),
addPeriodTypeAccts = Table.AddColumn(addCurrentPdAccts, "periodTypeAccts", each if [currentPdAccts] = "History" then "Actual" else "Forecast"),
chgAllTypes = Table.TransformColumnTypes(addPeriodTypeAccts, {{"date", type date}, {"finYear", type text}, {"finPeriod", Int64.Type}, {"finYearPeriod", Int64.Type}, {"finHY", type text}, {"finQtr", type text}, {"month", type text}, {"relativeFY", type text}, {"currentPdAccts", type text}, {"periodTypeAccts", type text}, {"monthYear", type text}, {"dayKey", Int64.Type}, {"day", type text}, {"monthKey", Int64.Type}, {"dayView", type text}, {"dayType", type text}, {"finWeekKey", Int64.Type}, {"currentMonth", type text}, {"relativeMonth", Int64.Type}, {"currentDay", type text}})
in
chgAllTypes
Pete