Hi @sedhosen.
(Edit: delete reference to inactive relationships; I was following two paths before I had success with one and forgot to clean-up; both post and PBIX attachment are now revised.)
As is often the case in Power BI, there are many ways to accomplish the same thing. Here’s one:
-
add a single date table (e.g., eDNA extended date table)
Extended Date Table (Power Query M function) -
as the dates of interest in the [Issues] table are at a “seconds” grain and the dates in the [Dates] table is at a “day” grain, in Power Query add two columns to your [Issues] table
- Issues[Created - Date Only]
- Issues[Resolved - Date Only]
- (for each, select the “base” column, duplicate it, then change its datatype to “Date”)
Then, add measures for your months:
Created Month-Year =
VAR _CurrentCreatedDate = SELECTEDVALUE( Issues[Created - Date Only] )
VAR _CurrentCreatedMonthYear = LOOKUPVALUE( Dates[Month & Year], Dates[Date], _CurrentCreatedDate )
RETURN
_CurrentCreatedMonthYear
Resolution Month-Year =
VAR _CurrentResolvedDate = SELECTEDVALUE( Issues[Resolved - Date Only] )
VAR _CurrentResolvedMonthYear = LOOKUPVALUE( Dates[Month & Year], Dates[Date], _CurrentResolvedDate )
RETURN
_CurrentResolvedMonthYear
Hope this help.
Greg
eDNA Forum - Single Date Table for Multiple Dates.pbix (562.5 KB)