How to Pass the Start Date and End Date values from Date Slicer to a measure or a calculated column?

[prasad3939]

Hello Folks,

I am trying to replicate a scenario form existing SSRS report into a Power BI report. Details as follows.

Use case and data:

The data is about a departement which creates Tickets for Incidents. Each Ticket comes with [Creation Date] and [Resolution date] (If the ticket is not resolved the resolution field is BLANK.

Logic in SSRS:

There are TWO parameters "Start date" and "End date". (User can select the start date and end date values of theri choice)

Two Date fields coming from data [Created date] and [Resolved date]

SamePeriod is the calculated Field: (Logic Given Below):

Basically the logic for this field is based on the start date and end date values (Parameter values). The below SSRS code compares if the Created date is >= Parameter START Date and Created date is <= to Parameter END Date AND Resolved Date >= Parameter Start date and Resolved Date <= Parameter End date THEN Count 1, Else 0.

It provides all the Tickets Resolved in the Selected period ( Between Start and End Date)

SSRS Logic:

=IIF(
(
FormatDateTime(Fields!CreatedDate.Value,DateFormat.ShortDate)>=FormatDateTime(Parameters!StartDate.Value,DateFormat.ShortDate)
And
FormatDateTime(Fields!CreatedDate.Value,DateFormat.ShortDate)<=FormatDateTime(Parameters!EndDate.Value,DateFormat.ShortDate)
And
FormatDateTime(Fields!ResolvedDate.Value,DateFormat.ShortDate)>=FormatDateTime(Parameters!StartDate.Value,DateFormat.ShortDate)
And
FormatDateTime(Fields!ResolvedDate.Value,DateFormat.ShortDate)<=FormatDateTime(Parameters!EndDate.Value,DateFormat.ShortDate)
),1,
0)

How can I replicate this in Power BI Desk top?

1) Now I want to create a Measure or a calculated field (Which ever is suitable) for the given logic and scenario.

2) I want to capture the start date and end date values from the Date slicer in Power BI and compare them against Created Date and Resolved Date and count those tickets that satisfies/ falls in the selected range category.

Slicer example from Power BI below

3) If which Date field I need to use for the date slicer (Created date? if not do I need to create any seperate date table to use for slicer dates?)

4) How can I capture the date range (Start and End date values) and pass that to a measure of calculated field Just as show in above SSRS logic and count the records that satisfies the logic?

Any help is greately appreciated.

Please ask if you have any questions

Thanks

[lbendlin · Accepted solution]

do I need to create any seperate date table to use for slicer dates?)

yes, you do need that.  Then you can simply use the VALUES() of that date column as your filter or as the TREATAS source.