Count rows based on a filter/date

[jiri81]

Hello,
I'm getting lost a bit here. For a specific visual I need to calculate Service Request created in a specific timeframe as well as service request escalated to the design team. This will be used to display on one visual (so I have the unrelated date table) since the time when the service requests get created and escalated to the design team will be different (day, month, year). Also, I'm planning to calculate a ratio to display  tickets to design escalation metrics 

Sample table looks like follows:

Service request table
Service Request ID; occurred; escalated to design;

where the occurred and escalated to design contains the date

I also have an unrelated calendar table
Date
Date;WeekNumber

I'm about to use DAX but seems that I'm getting it all wrong

Count Service Requests occurred  = CALCULATE(COUNTA('Service request table'[Service Request ID]]), FILTER(ALLSELECTED('Service request table'), 'Service request table'[Occurred]=SELECTEDVALUE('Date'[Date])))

and 

Count Service Requests escalated to design = CALCULATE(COUNTA('Service request table'[Service Request ID]]), FILTER(ALLSELECTED('Service request table'), 'Service request table'[escalated to design]=SELECTEDVALUE('Date'[Date])))

I have then a visual with the axis DATE (from the unrelated table) but does not display anything.  The same wit ha card when I apply filter.  I'm struggling to understand DAX and the DATE filter logic. 

Appreciate if you can point me to the appropriate direction with this.

Cheers,

Jiri

[BA_Pete · Accepted solution]

Hi @jiri81 ,

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

[jiri81]

Thank you for your response, Pete,

I was trying to avoid using the relationship because whenever I do it, it "breaks" the Data Hierarchy on the [Occurred]. I tied Active as well as Inactive buy always the same. This apparently breaks existing reports and visuals. Is there any way to avoid this? Shall I create [Copy of Occurred] to do so? 

Regardless, I was playing with this and still not getting the expected result. Will try again but will appreciate any guidance on this.

Edit after a coup of tea: 

My date table has the date in format  is 1.1.2020 0:00:00

While my occurred is always in 1.1.2020 10:30:15 or whatever was the exact time. I'm going to add columns and strip off the time

Jiri

[jiri81]

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

[BA_Pete]

Hi @jiri81 ,

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