Single Date filter for Multiple Columns and add Measures

[Kostas]

Hello, 

I am currently having an issue that it has to do with both, how to develop functions and how to do the calculations.

I have a table that contains a unique ID and multiple column dates (see print screen). I need in my visual to have a single date filter and the user to be able to see different results within the specified period in the filter. 

The results will always be a DistinctCount ID and then each bar (I am thinking to create a waterfall with different measure values) to have each own calculations.

Example of the calculations may be:

Count of ID -  (“Date1” before start of period) AND ((“Date2” blank) OR (“Date3” after beginning of period))

Count of ID– (“Date2” between start of period and end of period)

Count of ID– (“Date1” between start and end of period)

Count of ID– (“date1” before end of period) AND ((“Date2” blank) OR (“Date3” after end of period))

I am not really need all the calculations but I will need from someone to provide me the logic of how to be able to create a single date slicer for all the columns and then how to be able to create measures that will show a count based on multiple parameters according to the selected date.

I found multiple articles raised by users in the forum. The best solution that I found was to unpivot the dates and connect it to calendar table but that will not solve the before or after time period or the multiple filter parameters. 

Any suggestions?

[amitchandak]

@Kostas , Refer this article. This uses more than one dates

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

[Kostas]

Hello @amitchandak , 

thank you for the response and the article is really helpfull. Especialy the use relationship function that I didn't know about.

I understand that I need to create a date table and place the date value in the filter. 

Also, I can see how I can take the count within the period that the user will select by connecting the two tables and use the USERELATIONSHIP function to create the measure but, how I will get the values before the period?

As in example one that I specified before, the perfect solution would be (Dateinfull column = Date column in date calendar table):

Open at Start of Period = CALCULATE(DISTINCTCOUNT([ID]), USERELATIONSHIP([Date1], 'Calendar'[Date in Full]) < 'Calendar'[Date in Full], ISBLANK([Date2]) || USERELATIONSHIP([Date2] , 'Calendar'[Date in Full]) > 'Calendar'[Date in Full])

But I cannot use the "userelationship" function in that way.

What I could do is to calculate individually all measures and the create the parent measure that will give me the final results but, 

The issue is on how to return all dates for the specific relationship that occur before or after the calendar date. 

Could you explain me the process and the use please?

Thanks

[amitchandak]

@Kostas , Usage is wrong. Can you share sample in table format and expected output.

[Kostas]

Hello @amitchandak 

Please find attached the table (it is the same as above). 

I need to perform the calculations explained above and place them into a visual or individual cards. 

The users need to be able to have a single date filter and to see the distinct count of ID's within the period that they will select.

The first example that mentioned is that I will need the distinct count of ID's that will meet two criteria:

1) The dates in the Date 1 column will be before the selected period (in the filter)

2) The dates in the Date 2 column will be either after the selected period or blank.

For Example if the user select the period 30 May to June 10 2020 the count should be 1 (ID = 4294)

I hope that make sense

ID	Date1	Date2
4294	05 May 2020	04 June 2020
3392	30 May 2020	09 June 2020
4217	31 December 2020	10 January 2021
4216	31 December 2020	10 January 2021
4215	31 August 2020	10 September 2020
4214	31 August 2020	10 September 2020
4213	30 September 2020	10 October 2020
4217	31 December 2020	10 January 2021
4216	31 December 2020	10 January 2021

Thanks in advance

Kostas

[v-yingjl]

Hi @Kostas ,

Based on your latest update, if I got it correctly, you can create a control measure like this and set the value as 1 to put it in the filter of the table visual:

Date filter =
VAR _mindate =
    CALCULATE ( MIN ( 'Date'[Date] ), ALLSELECTED ( 'Date' ) )
VAR _maxdate =
    CALCULATE ( MAX ( 'Date'[Date] ), ALLSELECTED ( 'Date' ) )
VAR _date1 =
    SELECTEDVALUE ( 'Table'[Date1] )
VAR _date2 =
    SELECTEDVALUE ( 'Table'[Date2] )
RETURN
    IF ( _date1 < _mindate && _date2 < _maxdate, 1, 0 )

If you want to count, just create a slimar measure and put it in the table visual:

Count =
VAR _mindate =
    CALCULATE ( MIN ( 'Date'[Date] ), ALLSELECTED ( 'Date' ) )
VAR _maxdate =
    CALCULATE ( MAX ( 'Date'[Date] ), ALLSELECTED ( 'Date' ) )
VAR _date1 =
    SELECTEDVALUE ( 'Table'[Date1] )
VAR _date2 =
    SELECTEDVALUE ( 'Table'[Date2] )
RETURN
    SUMX ( 'Table', IF ( _date1 < _mindate && _date2 < _maxdate, 1, 0 ) )

The result is like this:

Here is the sample file hopes to help you solve this issue, please try it: PBIX 

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[Kostas]

hello @v-yingjl 

I can see the logic behind your code and thanks for the help. 

Unfortunately when I am applying the date measure in the filter section as a way for the filter to show only the values with "1" it automatically only shows to be the advance filter and I cannot apply any values inside the box. So I cannot test it. 

Any ideas why it does not let me apply any filters? When I also tried to place the measure into a slicer it did not let me to.

If you believe that is an application issue I will accept the solution as it seems that should work.

Thanks 

Kostas

[v-yingjl]

Hi @Kostas ,

Have you tried my sample file attached previously? This measure will work fine in the table visual. However you cannot put it in a card visual filter. Even you put it in the filter, you cannot set its value as 0 or 1 in the card visual. The card visual is not supported to put this control measure in it currently.

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[Kostas]

Hey @v-yingjl ,

I copied paste your measures into my pbix report. 

I placed the open in period measure as you did into the table and tried to apply filter on the table the date measure. 

When I am placing the date measure as filter I cannot change anything in the filtering or place the number ( 1 as you did).

See print screen. 

I just re-installed to my machine the Power BI bu still nothing. 

Thanks

[v-yingjl]

Hi @Kostas ,

I have modified the count measure like this:

Total =
VAR _mindate =
    CALCULATE ( MIN ( 'Date'[Date] ), ALLSELECTED ( 'Date' ) )
VAR _maxdate =
    CALCULATE ( MAX ( 'Date'[Date] ), ALLSELECTED ( 'Date' ) )
VAR tab =
    SUMMARIZE (
        'Table',
        [ID],
        [Date1],
        [Date2],
        "Flag",
        VAR _date1 = 'Table'[Date1]
        VAR _date2 = 'Table'[Date2]
        RETURN
            IF ( _date1 < _mindate && _date2 < _maxdate, 1, 0 )
    )
RETURN
    SUMX ( tab, [Flag] )

When you use Date filter measure to set up value as 1 in the table visual, it will show the correct result in the card visual:

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[Kostas]

Hello @v-yingjl ,

Thanks for the help and your code.

Indeed with the new code I was able to get the correct results in the table as it allowed me to place the filter of date in the filter section.

Unfortunately I cannot still make the card show the correct results.

Are you able to share the pbix file with me please?

Thanks in advance 

Kostas

[v-yingjl · Accepted solution]

Hi @Kostas ,

Here is the .pbix that you can try it again: PBIX 

Put the Date filter measure in the table visual filter and set up value as 1, put the Total measure in a card visual direcrtly and it should work fine.

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler]

Create a single, disconnected date table. Use Measures to get the dates selected by the user and then filter as appropriate.