cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Date parameter to calculate No of days open

Hi,

 

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

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

2 ACCEPTED SOLUTIONS

@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!

View solution in original post

@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!

 

View solution in original post

18 REPLIES 18
nsrshkh1
Resolver IV
Resolver IV

@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
Not applicable

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
Not applicable

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 

 

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
Not applicable

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.

No of Days Open.PNG

 

 

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 

 

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
Not applicable

Hi,

 

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

No of Days Open1.PNG

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 

 

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
Not applicable

Still the result is wrong, see the below output,

 

No of Days Open2.PNG

 

@Anonymous 

 

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

 

 

Anonymous
Not applicable

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,

No of Days Open3.PNGAppreciate  quick help on this.

 

@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!

View solution in original post

Anonymous
Not applicable

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,

No of Days Open4.PNGAt 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

No of Days Open5.PNG

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

@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
Not applicable

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 

 

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!

 

View solution in original post

Anonymous
Not applicable

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.

No of Days Open.PNG

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

Anonymous
Not applicable

 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.