Hello,
Thank you so much for your response. I found your response tremendously helpful, however since my post a few days ago, I am trying a different approach that I believe will get me closer to my goal. As well, I am avoiding the use of time intelligence functions in the DAX measure itself since I've tried them previously and the result was inconsistent between Power BI Desktop and Web Service instances. Meaning, the Desktop instance would show me one value and the Web Service would display another. Therefore, I went the following route:
I imported the two data tables (from a SQL Database) onto Power BI. Aforementioned in my previous post, one table is called '@PBI_OPEN_QUOTES_REPORT' and the other is called HolidayList (the holiday table), however the latter now has all dates in the year 2024 as opposed to just the office holiday dates.
1) '@PBI_OPEN_QUOTES_REPORT' has the following data columns:
-DocNum
-TomorrowDate
-TodayDate
-Within 24 Hours Flag
-PI/PC Flag
-Quote Status
-CurrentTime
-DueDateTime
-Due Date
-Due Time
Definitions for each column are:
DocNum - unique identifier representing a Quote ID
CurrentTime - displays current date and time
TodayDate - display's today's date based on CurrentTime
TomorrowDate - displays tomoprrow's date using CurrentTime
Quote Status - displays status of a quote
PI/PC Flag - searches data values of the Quote Status column and, if there is a status of 'PI', then it will output a value of 'Y'
Due Date - displaus the date that a quote is due
Due Time - displays the time of day the quote is due
DueDateTime - combines the Due Date and Due Time columns together to show the due date and time a quote is due in the same cell
Within 24 Hours Flag - outputs a value of 'Y' if the DueDateTime value is within 24 hours of the CurrentTime
2) HolidayList has the following data columns:
DATE
Holiday
Day
IsWorkingDay
Time
Definitions for each column:
DATE - displays a date with the day of the week, month, day and year
Holiday - displays if there is a holiday thaqt day, otherwise says 'N/A'
Day - assigns each day of the week with a number 1-7 (1 is Monday, 2 is Tuesday, and follows that pattern until Sunday, which is 7)
IsWorkingDay - dictates if that day is a working day by displaying either 'Working' or 'Non-Working'. Normal working days (weekdays, where Day value is 1-5, is 'Working', while weekends where Day value is 6 or 7 and holiday days are 'Non-Working')
Time - displays a time value
My objective is to create a measure that will display the number of quotes (based on Quote ID's from the DocNum column on the '@PBI_OPEN_QUOTES_REPORT' table) that, by default, meet the following conditions:
-The value of the PI/PC Flag column on the '@PBI_OPEN_QUOTES_REPORT' table for that Quote ID is 'Y'
-The value on the Within 24 Hours Flag column on the '@PBI_OPEN_QUOTES_REPORT' table for that Quote ID is 'Y'.
However, the measure must also take the following scenario into account and update data accordingly in addition to meeting the above conditions, meaning:
-If the value on the TomorrowDate column on the '@PBI_OPEN_QUOTES_REPORT' table is the same value as the DATE column on the HolidayList table where the IsWorkingDay column value is 'Non-Working' (which would either be the result of a weekend Day or a Holiday Day, then the 'Within 24 Hours Flag column data value should therefore show a value of 'Y' and thus be included in this measure's output.
The closest I have gotten is configuring the following measure:
Within 24 Hours =
VAR TomorrowDateWithNotWorking =
CALCULATETABLE(
FILTER(
'HolidayList',
'HolidayList'[IsWorkingDay] = "Non-Working" &&
'HolidayList'[DATE] = MAX('@PBI_OPEN_QUOTES_REPORT'[TomorrowDate])
)
)
RETURN
VAR TOTALCOUNT =
CALCULATE (
DISTINCTCOUNT('@PBI-OPEN_QUOTES_REPORT'[DocNum]),
'@PBI_OPEN_QUOTES_REPORT'[PI/PC Flag] = "Y",
'@PBI_OPEN_QUOTES_REPORT'[Within 24 Hours Flag] = "Y"
)
RETURN
IF (
MAX('@PBI_OPEN_QUOTES_REPORT'[Within 24 Hours Flag]) = "Y"
ll COUNTROWS(TomorrowDateWithNonWorking) > 0,
TotalCount,
0
)
Please advise on what relationships I'd have to establish between the two tables (so far I've tried creating a One to Many, single relationship between HolidayList[DATE] and '@PBI_OPEN_QUOTES_REPORT'[TomorrowDate]) and how I can augment the measure I have (or create a new one) encompassing all of the above.
