cancel
Showing results for
Did you mean:

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

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
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]),
)``````

Pete

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

Proud to be a Datanaut!

Super User

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"}}),
([date])),2)}, " ")),
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

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

Proud to be a Datanaut!

8 REPLIES 8
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

Super User

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

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

Proud to be a Datanaut!

New Member

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!

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]),
)``````

Pete

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

Proud to be a Datanaut!

New Member

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.

Super User

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!

New Member

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

Super User

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"}}),
([date])),2)}, " ")),
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

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

Proud to be a Datanaut!

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors