Forum Discussion

Rinshe's avatar
Rinshe
New Member
3 years ago
Solved

Dynamic Date Slicer with Parallel Dynamic Period

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...
Need Help
  • BA_Pete's avatar
    BA_Pete
    3 years ago

    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

  • BA_Pete's avatar
    BA_Pete
    3 years ago

     

    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

BA_Pete's avatar
BA_Pete
Super User
3 years ago

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's avatar
Rinshe
New Member
3 years ago

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's avatar
    BA_Pete
    Super User
    3 years ago

    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's avatar
      Rinshe
      New Member
      3 years ago

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

      • BA_Pete's avatar
        BA_Pete
        Super User
        3 years ago

         

        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