Dynamic Date Slicer with Parallel Dynamic Period

[Rinshe]

I have a column which has incident IDs in text and a date column in date time format. I want to set a slider in a page using that date column so that dynamically i can distinct count the incident IDs for the dates selected. I also want to calculate the distinct count of Incident IDs for the parallel period (in days). For example if i select the slicer, then my start of this period = 3-6-2022 and end of this period =9-9-2022, then the date difference in days between them is 98 days which translates to start of previous period = 24-2-2022 and end of previous period =2-6-2022 with the same days in difference between them. And i want to get distinct count of incident IDs for both this and previous period. I am having trouble to get the previous period Distinct count of Incident IDs. Any help would be great!

[BA_Pete · Accepted solution]

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

[Rinshe]

I have created the proper calendar table as you have mentioned, now i have given a relationship between the Calendar [date] and the date column in the incident table. The date hierarchy for the date column in the incident table disappeared as I mentioned earlier. Ignoring that, i tried to use the Calendar[date] hierarchy in my visuals and it does not drill down the incident ID as expected. It still shows the overall year level value how much ever I drill down based on the calendar[date] hierarchy

[BA_Pete]

Once you have your related calendar table, there are two ways to create drilldown hierachies:

1) Build the hierarchy however you want it in the visual axis, like this:

OR

2) In the Fields list on the Modelling tab, select the highest level of your hierarchy (finYear in my case), click the ellipsis, and select Create Hierarchy:

You can then drag and drop new hierarchy levels into that hierarchy to end up with something like this:

And you can drag the whole hierarchy into your axis to get the same behaviour as the default functionality:

*NOTE*I don't believe method 2 works in Live Connection models, and it may not even work in Direct Query or Mixed models either.

Pete

[Rinshe]

That is solved, i had the date format as date time in the incident table date column, that is the reason it was showing an error. Now I am working on the way you have given to count the incident IDs for previous period. Thanks for all the help!

[BA_Pete · Accepted solution]

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

[Rinshe]

I tried creating the proper calendar table and linking it with the date column with One:Many relationship but doing that makes the date hierarchy to disppear from the date column which affects the other visuals in the dashboard. 

[BA_Pete]

Hi @Rinshe ,

In order to use Power BI time intelligence functions you need a calendar table.

Any date hierarchies you are using in your visuals should be recreated in your calendar table and used from there instead. You'll find this makes your reportfiles much smaller too, as each automatically-generated date hierarchy in Power BI is a separate calendar table in the background.

If you need help creating a calendar table with the hierarchies in, let me know what time periods you use (month, quarter etc.) and whether they are financial periods or calendar periods, and I should be able to knock something up for you.

Pete

[Rinshe]

Thanks for that info! Much Appreciated on the help! I would need Year,Quarter,Month, Month No and Day

[BA_Pete · Accepted solution]

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