Dynamic Date Slicer with Parallel Dynamic Period

Hi Rinshe ,

You'll need a proper calendar table related to your incident fact table on calendar[date] ONE : MANY incidentTable[incident date].

Once you have this, the measures would be as follows:

_incidentsSelected = DISTINCTCOUNT(incidentTable[Incident ID])

_incidentsPriorPeriod =
VAR __noofDays =
DISTINCTCOUNT(calendar[date])
RETURN
CALCULATE(
    DISTINCTCOUNT(incidentTable[Incident ID]),
    DATEADD(calendar[date], - __noofDays, DAY)
)

Pete

Here's a basic calendar to get you started.

In Power Query, create a new blank query, then paste this code over all of the default code in there:

let
  // Define Date.Today
  Date.Today = Date.From(DateTime.LocalNow()),
  // Build calendar
  Source = { Number.From(#date(2015,1,1))..Number.From(#date(2022,12,31)) },
  convToTable = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
  chgDateType = Table.TransformColumnTypes(convToTable, {{"Column1", type date}}),
  renCols = Table.RenameColumns(chgDateType, {{"Column1", "date"}}),
    addYear = Table.AddColumn(renCols, "year", each Date.Year([date])),
    addRelativeYear = Table.AddColumn(addYear, "relativeYear", each [year] - Date.Year(Date.Today)),
    addQuarterKey = Table.AddColumn(addRelativeYear, "quarterKey", each Date.QuarterOfYear([date])),
    addRelativeQuarter = Table.AddColumn(addQuarterKey, "relativeQuarter", each ([year] * 4 + [quarterKey]) - (Date.Year(Date.Today) * 4 + Date.QuarterOfYear(Date.Today))),
    addMonthKey = Table.AddColumn(addRelativeQuarter, "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)}, " ")),
  addRelativeMonth = Table.AddColumn(addMonthYear, "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)),
  addRelativeDay = Table.AddColumn(addDay, "relativeDay", each [date] - Date.Today),
    chgTypes = Table.TransformColumnTypes(addRelativeDay,{{"year", Int64.Type}, {"relativeYear", Int64.Type}, {"quarterKey", Int64.Type}, {"relativeQuarter", Int64.Type}, {"monthKey", Int64.Type}, {"relativeMonth", Int64.Type}, {"dayKey", Int64.Type}, {"relativeDay", Int64.Type}, {"month", type text}, {"monthYear", type text}, {"day", type text}})
in
    chgTypes

If you want to change the dates the calendar runs over, just change the dates in the source line here:

Personally, I would recommend putting this into a dataflow and refreshing it around 00:30 your local time each night. It will then be immediately available to any project you need a calendar for.

Pete