Count Number Range Date

[powertechbi]

Good afternoon

I have a spreadsheet that shows me the period of entry and exit of a patient and the total number of days.
I need to calculate the total number of days that are within the date filter period.
In the example I highlighted I have a range from 09/27 to 10/07. I only need to calculate the days of the month that are in the filter (September).

[OktayPamuk80 · Accepted solution]

Hi,

You could do following:

- You have a calendar table using calendar = CALENDARAUTO()

- In your existing table, I assume you have something like Patientnumber/ID 

- Using below formula to create a table in the table view using DAX, you can have the a new table with patient and each day between the dates in/out:

Solution =
SELECTCOLUMNS(

      GENERATE('Table', DATESBETWEEN('calendar'[Date],'Table'[in], 'Table'[out])),
     "Patient",'Table'[Patient],
     "Month",'Table'[mes],
     "PatientDate", 'calendar'[Date]

)

Can you try this out?

Regards,

Oktay

Did I answer your question? Then please mark my post as the solution.

If I helped you, click on the Thumbs Up to give Kudos.

[danextian · Accepted solution]

Hi @powertechbi ,

Using a separate dates table, you can write a measure that calculates the row count  within a specific date ranges.

Days Count = 
CALCULATE (
    COUNTROWS ( Dates ),
    KEEPFILTERS (
        Dates[Date] >= SELECTEDVALUE ( StartEnd[Start] )
            && Dates[Date] <= SELECTEDVALUE ( StartEnd[End] )
    )
)

 Please see attached sample pbix.

[OktayPamuk80 · Accepted solution]

Hi,

You could do following:

- You have a calendar table using calendar = CALENDARAUTO()

- In your existing table, I assume you have something like Patientnumber/ID 

- Using below formula to create a table in the table view using DAX, you can have the a new table with patient and each day between the dates in/out:

Solution =
SELECTCOLUMNS(

      GENERATE('Table', DATESBETWEEN('calendar'[Date],'Table'[in], 'Table'[out])),
     "Patient",'Table'[Patient],
     "Month",'Table'[mes],
     "PatientDate", 'calendar'[Date]

)

Can you try this out?

Regards,

Oktay

Did I answer your question? Then please mark my post as the solution.

If I helped you, click on the Thumbs Up to give Kudos.

[Ashish_Mathur]

Hi,

Your question is not clear.  Share data in a format that can be pasted in an MS Excel file.  Show the expected result very clearly.