Date parameter to calculate No of days open

[Anonymous]

Hi,

I have Entry date field in my data which looks as below,

I would like to have an date parameter called as period Close where user can type in the date or choos the date

Period Close: 31/03/2019.

Now based on my period close value I should be able to get a calcuated field as

No of days open = DateDiff("dd",Date([Entry Date]),Date('${PeriodClose}'))

then I need count of document number where No of days open>15.

How can we achieve this using Period Close parameter or slicer.

Quick help is Appreciated.

Regards

Harsha

[Anonymous · Accepted solution]

@Anonymous 

I am not sure what challenge you are facing

attached is the file for your reference.

https://www.dropbox.com/s/1o6krk4xe8qmpv4/Date%20parameter%20to%20calculate%20No%20of%20days%20open.pbix?dl=0

You can share your file via Google drive, One Drive or drop box

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

[Anonymous]

@Anonymous 

1) Create a Date Table for slicers

DateTable = VALUES('Table'[Date])

2) Created a Measure to store selected date

DateSelected = IF(ISFILTERED(DateTable[Date]),FIRSTNONBLANK(DateTable[Date],1),BLANK())

3) Create a Measure to calculated the total count

TotalCount = CALCULATE(COUNTA('Table'[Product]),FILTER('Table',('Table'[Date]-[DateSelected])>15))

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

[Anonymous]

Hi,

Thanks for the quick reply.

Mostt of it's working as required but I would like to know how to get No of days open insted of total count

I am using the below formula but it's not giving any data,

No of Days Open = DATEDIFF([Entry Date],'Date Table'[Date Selected],DAY).
can you correct the calcualtion for No of days open?

Quick help would be appreciated.
 
Regards
Harsha

[Anonymous]

Hi,

Thanks for the quick reply.

Mostt of it's working as required but I would like to know how to get No of days open insted of total count

I am using the below formula but it's not giving any data,

No of Days Open = DATEDIFF([Entry Date],'Date Table'[Date Selected],DAY).

can you correct the calcualtion for No of days open?

Regards

Harsha

[Anonymous]

@Anonymous 

Try this !

No Of Open Days = CALCULATE(COUNTA('Table'[Date]),FILTER('Table',('Table'[Date]-[DateSelected])>0))

If you want to include the date selected

No Of Open Days = CALCULATE(COUNTA('Table'[Date]),FILTER('Table',('Table'[Date]-[DateSelected])>=0))

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

[Anonymous]

Hi,

The calculation that you had shared is not giving me the difference. it's to calculate the count of date.

see the below output after using the calculations. bith total count & No of days open are giving same results.

But I am expecting the diff between Entry date & Selected Date it should be

Selected Date- Entry Date

Ex: 31/03/2019 - 29/03/2019 = 2 Days.

Quick help would be appreciated.

[Anonymous]

@Anonymous 

Sorry for confusion

Try this

1) 

No Of Open Days =
IF (
    FORMAT ( LASTNONBLANK ( 'Table'[Date], 1 ) - [DateSelected], 0 ) + 0 < 0,
    0,
    FORMAT ( LASTNONBLANK ( 'Table'[Date], 1 ) - [DateSelected], 0 ) + 0
)

2)

No Of Open Days1 =
IF (
    FORMAT (
        CALCULATE (
            SUMX (
                ADDCOLUMNS ( 'Table', "NoOfDays", 'Table'[Date] - [DateSelected] ),
                [NoOfDays]
            )
        ),
        0
    ) + 0 <= 0,
    0,
    FORMAT (
        CALCULATE (
            SUMX (
                ADDCOLUMNS ( 'Table', "NoOfDays", 'Table'[Date] - [DateSelected] ),
                [NoOfDays]
            )
        ),
        0
    ) + 0
)

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

[Anonymous]

Hi,

Used the formulas but still not getting req output. see the below results after using the caculations.

it's populating same value in No of days open and finally result is 0 every where.

what I am expecting is to substract the Entry Date, Selected Date

Selected Date-Entry Date.

Quick help would be appreciated.

[Anonymous]

@Anonymous 

If you want to see negative difference also then remove IF condition.

No Of Open Days1 =
FORMAT (
    CALCULATE (
        SUMX (
            ADDCOLUMNS ( 'Table', "NoOfDays", 'Table'[Date] - [DateSelected] ),
            [NoOfDays]
        )
    ),
    0
) + 0

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

[Anonymous]

Still the result is wrong, see the below output,

[Anonymous]

@Anonymous 

Would you send me the sample data to analzyze the issue?

[Anonymous]

Hi,

I don't have option to attach .Pbix file here, Jus explaining any generic data that has date field ex: order date from 2016 to 2019. we should create date table using order date as you had mentioend earlier and writing calculation as Selected date = selectedvalue(order date).

No of days diff = datediff(order date,selected date,day). but this is givng me nothing.

see the below scrrenshot use it as sample data,

Appreciate  quick help on this.

[Anonymous · Accepted solution]

@Anonymous 

I am not sure what challenge you are facing

attached is the file for your reference.

https://www.dropbox.com/s/1o6krk4xe8qmpv4/Date%20parameter%20to%20calculate%20No%20of%20days%20open.pbix?dl=0

You can share your file via Google drive, One Drive or drop box

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

[Anonymous]

Hi,

it's perfectly working fine. almost 90% of my requirement achieved. Thanks for the solution.

I have a final question here, Is there any way that we can keep No Of Open Days2 in page level filter? because unless we use date fields in view it's not giving me the proper results. which means if I use all req fields then I can filter based on no of days open2 example: No of days open2>15. see the result below,

At the same i won't be able to use No of days open2 in Page filter.

with out using the date fileds it's not showing proper result filtering by No of Days Open2>15

from your example, I want to show amount by product where No Of Open Days2 >15 in any graph and it should be page level filter. desired ouput for amount is: 264479 instead of 390974

but still it's showing 390974 for products P1,P2,P3 beloe as it's not allowing to use No of days open2 in page filter/visual filter.

see the below for reference

Thanks for your help so far, please be advice the best way to sollve the above issue.

[Anonymous]

@Anonymous 

Today is your lucky day !

Use this Measure for calculating final your amount. Unfortunately we cannot yet use measures as slicers in Power BI.

FinalAmount = CALCULATE(SUM('Table'[Amount]),FILTER('Table',[No Of Open Days2]>15))

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

[Anonymous]

Hi

Thanks for the solution.

is there a way that we can make Date slicer as free text? means type in the date in slicer rather than selecting the dates.

ex: user can type in their date as 31/03/2019 based on it no of days will calculate/

so far we have date slicer it has all dates and we are using list or dorpdown. instead is there any way to make date slicer as free text?

[Anonymous · Accepted solution]

@Anonymous 

You can change slicer type from dropdown to After/Before by clicking on down arrow on Slicer header.

Use this measure if not done before for Date Selected

DateSelected = IF(ISFILTERED(DateTable[Date]),FIRSTNONBLANK(DateTable[Date],1),BLANK())

For any other queries please raise another thread and close this one.

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

[Anonymous]

Hi,

The calculation that you had shared is not giving me the difference. it's to calculate the count of date.

see the below output after using the calculations. bith total count & No of days open are giving same results.

But I am expecting the diff between Entry date & Selected Date it should be

Selected Date- Entry Date

Ex: 31/03/2019 - 29/03/2019 = 2 Days.

help me out on this.

Regards

Harsha