Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Rinshe
New Member

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 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!

2 ACCEPTED SOLUTIONS
BA_Pete
Super User
Super User

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




View solution in original post

 

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:

BA_Pete_0-1663140321384.png

 

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




View solution in original post

8 REPLIES 8
Rinshe
New Member

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

 

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:

BA_Pete_0-1663155619524.png

 

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:

BA_Pete_1-1663156429637.png

 

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

BA_Pete_2-1663156948631.png

 

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

BA_Pete_3-1663157003477.png

 

*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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




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
Super User
Super User

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




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. 

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




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

 

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:

BA_Pete_0-1663140321384.png

 

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors