How to count user input along with blank field od date column for different periods

[saivina2920]

I have 3 different periods (<10 day, 10-20 Days, >20Days)

I have 2 fields EMP_RESIGN_DATE and EMP_RELIEVE_DATE.

my requirement is,

when user entered input in input box (TextBox or Parameter) based on that
my 3 different periods should calculate.

For Example, whereever the blank cell available in the column of EMP_RELIEVE_DATE

then we have to take corresponding column value of EMP_RESIGN_DATE + "User Input Field"

User Input ==> 5

If found any blank available in the field of EMP_RELIEVE_DATE,
then "get the user input" value + add in EMP_RESIGN_DATE ==> Refer "H" Col.

Here, If i haven't entered any input it should consider only "EMP_RESIGN_DATE"

Below i have defined 3 different periods measure. This will work, without entered user input.

If i entered any input, then if found any blank in "EMP_RELIEVE_DATE", the process sholud calculate based on the input.

vToday = TODAY()
vCalDate10 = EMP_TABLE[vToday] - 10
vCalDate20 = EMP_TABLE[vToday] - 20

<10Days =
CALCULATE(DISTINCTCOUNT(EMP_TABLE[EMP_NO]),FILTER(EMP_TABLE,
EMP_TABLE[EMP_RESIGN_DATE] >= (EMP_TABLE[vCalDate10]) && EMP_TABLE[EMP_RESIGN_DATE] <= (EMP_TABLE[vToday]) && EMP_TABLE[EMP_STATUS] = "Working"))

10-20Days =
CALCULATE(DISTINCTCOUNT(EMP_TABLE[EMP_NO]),FILTER(EMP_TABLE,
EMP_TABLE[EMP_RESIGN_DATE] >= (EMP_TABLE[vCalDate20]) && EMP_TABLE[EMP_RESIGN_DATE] <= (EMP_TABLE[vCalDate10]) && EMP_TABLE[EMP_STATUS] = "Working"))

>20Days =
CALCULATE(DISTINCTCOUNT(EMP_TABLE[EMP_NO]),FILTER(EMP_TABLE,
EMP_TABLE[EMP_RESIGN_DATE] <= EMP_TABLE[vCalDate20] && EMP_TABLE[EMP_STATUS] = "Working"))

If user will not entered input, the above expression count has come ==> Reference column is "EMP_RESIGN_DATE"

if user entered input, the above expression should vary based on the input and our reference date column is

"EMP_RELIEVE_DATE" + user input + non blank of EMP_RESIGN_DATE

Final output would be like below. 

[v-jingzhang]

Hi @saivina2920 

I'm not sure if I understand your requirement correctly. If users don't enter a value for the parameter (parameter value is blank), you want the measures refer to "EMP_RESIGN_DATE" column to filter the table. If users enter a value for the parameter, you want to add a new column to the table ("H" col in your Excel screenshot) and refer to this new column to filter the table in these measures. If I understand it correctly, you can download the attached PBIX below to check whether it meets your need. If not, let me know where I am wrong.

For each period, I use measures like below. "new_EMP_RELIEVE_DATE" is a new column added in a table variable. And the last measure is what you need.

<10Days = 
CALCULATE(DISTINCTCOUNT(EMP_TABLE[EMP_NO]),FILTER(EMP_TABLE,
EMP_TABLE[EMP_RESIGN_DATE] >= [vCalDate10] && EMP_TABLE[EMP_RESIGN_DATE] <= [vToday] && EMP_TABLE[EMP_STATUS] = "Working"))

<10Days 2 = 
var _t = ADDCOLUMNS(EMP_TABLE,"new_EMP_RELIEVE_DATE",IF(ISBLANK(EMP_TABLE[EMP_RELIEVE_DATE]),[EMP_RESIGN_DATE] + Parameter[Parameter Value],EMP_TABLE[EMP_RELIEVE_DATE]))
var result = CALCULATE(DISTINCTCOUNT(EMP_TABLE[EMP_NO]),FILTER(_t,[new_EMP_RELIEVE_DATE] >= [vCalDate10] && [new_EMP_RELIEVE_DATE] <= [vToday] && [EMP_STATUS] = "Working"))
return
result

<10Days 3 = IF(ISBLANK(Parameter[Parameter Value]),[<10Days],[<10Days 2])

Kindly let me know if this helps.
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.

[saivina2920]

Really. you are great. what ever you are given logic is almost same.

I forgot to include one main condition here. this is main condition.

The chart and table count for the periods should come based on selection of "EMP_STATE"  only.

We have derive the count before and after enter user input ==> this is my requirement

pls. refer the attachment for more details.

If user will selected "EMP_STATE" = "Alaska" and user will not enter any input.

The total records for the above selection is 3 blanks and 2 non blank of "EMP_RELIEVE_DATE"

Now, we have to process only for the 2 non blank of "EMP_RELIEVE_DATE".

now the final count (before enter user input :

<10 Days : 2

if user selected "EMP_STATE" = "Alaska" and user will enter input "10"

so now, the "EMP_RELIEVE_DATE" is ==> 10+"EMP_RELIEVE_DATE"

Once we processed blank data, then we have to add non blank data also.

So, total record would be "5" Rows (After enter user entered input).

and the final count would be (after entered user input)

<10 Days :2

>20 Days : 1

pls. find the TEST File for your reference.

https://1drv.ms/u/s!AiSRcgO5FUmN8SeMMTsWv00pGkE5?e=yi1952 

[saivina2920]

[saivina2920]

pls. let me know if need any more details.

[v-jingzhang]

@saivina2920 I find there is an error in your sample data. For EMP-012 where EMP_RESIGN_DATE is 12/28/2021, it should be 12/28/2020, or you will not get it counted in >20Days period. It is in the future.

[v-jingzhang]

Hi @saivina2920 

You can use [<10Days 2], [10-20Days 2], [>20Days 2] measures directly in your .pbix file. I think they will achieve your requirement.

[saivina2920]

Thanks. Future date should not be the case and this should not come in the count.

I tried [<10Days 2], [10-20Days 2], [>20Days 2] measures directly.

but, still showing the blank cell rows count. (because i haven't enter any user input. So, it should not come in the blank rows count)

After Entering Input, below is the output.

[v-jingzhang]

@saivina2920 

It is because the new_EMP_RELIEVE_DATE column is added in a table variable in measures not in the actual table, so you will not see the data. When you select EMP_RELIEVE_DATE column in the table visual, it still uses the original data.

Since the pamameter value is not able to influence a calculated column dynamically, I think we need to create a measure to display the new relieve date value in the table visual. Will need some time to test this.

Regards,
Community Support Team _ Jing

[saivina2920]

Yes. exacly you are correct. now, i hope understood the concepts.

Take time to test and i am waiting. paralelly i am also trying.

[v-jingzhang]

@saivina2920 

You can create a measure with below codes to display the new relieve date in the table visual.

new_RELIEVE_DATE = 
IF (
    ISINSCOPE ( EMP_TABLE[EMP_NO] ),
    SWITCH (
        ISBLANK ( Parameter[Parameter Value] ),
        TRUE (),
            IF (
                ISBLANK ( SELECTEDVALUE ( EMP_TABLE[EMP_RELIEVE_DATE] ) ),
                BLANK (),
                SELECTEDVALUE ( EMP_TABLE[EMP_RELIEVE_DATE] )
            ),
        FALSE (),
            IF (
                ISBLANK ( SELECTEDVALUE ( EMP_TABLE[EMP_RELIEVE_DATE] ) ),
                SELECTEDVALUE ( EMP_TABLE[EMP_RESIGN_DATE] ) + Parameter[Parameter Value],
                SELECTEDVALUE ( EMP_TABLE[EMP_RELIEVE_DATE] )
            )
    ),
    BLANK ()
)

And I add two filters to the table visual. You can hide them if you don't want report users to change these filters.

[Relieve date flag] measure is used to determine whether the new relieve date is in the future or blank or in the past. If it is in the future or blank, the measure will return 0 and these rows will not display in the visual. 

Relieve date flag = IF([new_RELIEVE_DATE]>[vToday] || ISBLANK([new_RELIEVE_DATE]),0,1)

For details, please download the attachment. Let me know if you have any questions.

Regards,

Jing

[saivina2920]

Yes. you are perfect. Everything is good except one are two.

Table value is coming perfect. but, chart and multi-row card count mismatching without user input.

below screenshot for your reference.

The other part, with user input all the values (Table,Chart and Multi-Row Card) coming perfect. below screenshot for your reference.

Where we need to change first screenshot..

[saivina2920]

can you pls. give us the update when you have time...?

[v-jingzhang]

Hi @saivina2920 

I think the cause is probably the boundary dates.

In [<10Days], it filters dates which are >= [vCalDate10]. While in [10-20Days], it filters dates which are >= [vCalDate20] and <= [vCalDate10]. If a date is happenly on [vCalDate10], it will be counted both in [<10Days] and [10-20Days]. The same happens on [vCalDate20] in the [10-20 days] and [> 20 days] measures. 

Only remain one equal sign on the boundry dates and test the measures with some boundry date values. 

[saivina2920]

I have only two records for the selection "Alaska". then how it will come 3 reocrds in pie chart an multi-row card. The correct count will reflect in Table chart.

Can you pls. give us more clarity on this..?

[v-jingzhang]

@saivina2920 Please download the attachment and see the page 2 tab. Although boundary date is not the cause, I remain only one equal sign in new measures. You can change that per your need.

[saivina2920]

How did you move all measures to one group measures...?

[v-jingzhang]

You can create a table by Enter data with any value, then move the measures to the table by changing their Home table. After that, delete the column in this table, close and reopen the pbix file.

Other reference: https://radacad.com/organize-power-bi-dax-measures-in-folders

BTW, are the new measures working?

[saivina2920]

Thanks. new measures working are working except some minor bug.

1. "new_RELIEVE_DATE" showing some invalid date like "19/01/1900". But, "EMP_RESIGN_DATE" data is there. (This will be happening after enter the input). 

The count is coming perfect,but, displaying the date improper.

Suppose if i add "EMP_RESIGN_DATE" field in table chart, then date is coming. remaining filter is same. below screenshot for reference.

2. By default, Chart (Pie Chart and Table Chart) count should be blank until or unless i am selecting "EMP_STATE" slicer input.

for example, if i select "EMP_STATE" = "Alaska" then only i should display the count in chart.

otherwise, table chart should be blank.

IF the above solved, mine is over.

pls. find the PBIX and test file in below url.

https://1drv.ms/u/s!AiSRcgO5FUmN8Spap0qCDsxGfe-b?e=GDZc7H

[saivina2920]

Thanks for your great support and followup.

pls. let us know, if you need more details and look at this when you have time. 

[saivina2920]

Any update pls.