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 solve the second one, but I'm not able to solve the first one. See the attachment.

The "new_RELIEVE_DATE" is a measure. A measure is calculated according to the context it is located in and a measure only returns an aggregate value. In this measure, it needs a specific "EMP_RESIGN_DATE" value to calcualte the correct new relieve date. And I use SELECTEDVALUE() to get the resign date. The SELECTEDVALUE() function will return blank if it gets more than one distinct value or blank value. 

For example, if you don't put "EMP_RESIGN_DATE" column into the table visual, EMP-610 with working status in Alaska will have two resign dates, so SELECTEDVALUE(EMP_TABLE[EMP_RESIGN_DATE]) will return blank and the new relieve date will be blank+input. However, because input is not blank and the measure should return a date value, it will pick Power BI's default start date 1899-12-30 for the blank date to calculate. 

So I suggest adding "EMP_RESIGN_DATE" column into the table visual to provide a more specific context for "new_RELIEVE_DATE" measure.

Regards,
Jing
If this post helps, please Accept it as the solution to help other members find it.

[saivina2920]

Thanks for your reply..

We have created measure for more filter and applying in that. Working fine.

Flag 2 = IF(ISFILTERED(EMP_TABLE[EMP_STATE]),1,0)

like that i have created more flags and i want to maintain those flags permenantly.

But, when clear all filter, then the above measure (flag 2,flag 3,flag 4,...etc) filter also cleared. this, I want to retain permenantly. otherwise, end user will get more confusion.

then only, i can get the correct count.

If this is done, then my all the issues are over..

pls. help.

[v-jingzhang · Accepted solution]

@saivina2920 Sorry for late reply. Just come back from vacation.

You can first set up the values of these filters, then update the existing bookmark. Do not click the bookmark. Instead, hover the mouse cursor over it and click ... (More options) to show the option menu and click Update. In this way, the bookmark will store the current status. You can also add a new bookmark to store the status and use it to replace the old one for the button action.

[saivina2920]

I am facing another issue and which i posted in another thread.

yes. i am not able to get today records in <5 days period. Why..

https://community.powerbi.com/t5/Desktop/Data-not-fetching-for-TODAY-DATE-in-DAX-measure/m-p/1764197...

vToday = TODAY()
vCalDate5 = [vToday] - 5
Emp Joined Date =
CALCULATE(DISTINCTCOUNT(EMP_TABLE[EMP_NUMBER]),FILTER(EMP_TABLE,
(EMP_TABLE[JOINED_DATE] >= [vCalDate5] && EMP_TABLE[JOINED_DATE] <= [vToday]

&& EMP_STATUS = "ACTIVE")

[saivina2920]

One last question.

I have tested all the cases. some places displaying Future Date also. we should truncate in the chart table.

Count has not displaying, but, date will be displaying.

how to avoid to display future date in new_Relieve_Date at chart table

[v-jingzhang]

Hi @saivina2920 

I remember there is a [Relieve date flag] measure in my pbix file which deals with this situation. You can modify it or create a new measure similar to it, and apply this measure as a filter on the table visual. I have description about this measure in my reply on 3/31/2021.

[saivina2920]

Thanks..it's working...

[saivina2920]

Finally we achieved. You are rocking. Thanks for all your smile and favourable replies.

[saivina2920]

Any update pls.

[saivina2920]

pls. update the same.

[saivina2920]

pls. update when you have time. Thanks for all your support.

[saivina2920]

test

[saivina2920]

test

[v-jingzhang]

Sorry, I find the cause is the new column added in the table variable in measures. The logic I dealt with the new relieve date when parameter value is blank was wrong. I gave it the resign date. Am trying to modify the codes.

[saivina2920]

pls. let us know if you need anymore details. almost we are in finishing stage.

[saivina2920]

any doubt or any more details required, pls. let me know.