# of Days Dynamic

[Anonymous]

Hello, Does anyone know how to cound the # of days dynamically, please?

I would like to calculate days between two dates, one date is fixed and the other is dynamic.

The 2 dates are : Start Date which is fixed and the end of the month selected month which needs to be dynamic.

So for example if the start date is Feb 14, 2022. If on the slicer the chosen month is Feb then the # of days should be 15, if start date is Feb 14, 2022 and on the slicer the chose month is March then the # of days should be 31 and if Mar and Feb is chosen the the # of days is 15 + 31 = 46.

I have been trying to do a Datediff dax formula however because the start date is a column, and the end date is end of the month, I can not figure out how to get the start date as a measure so I can use datediff in dax on a slicer. Can anyone help please?

[Anonymous]

Hi VahidDM and Happy Monday to you 🙂

Thanks, but I already have a date table and I already used datediff to come up with it the first part of the problem.

The problem, I am having is coming up with the part of the if statment that either uses the Target Start Date or the start of the month. I guess i would have to use an if statement that says if less than start date then use start of month or something like that

Here is the formula that I have so far but I am still trying to figure out how to do the if statement part, so if you could be of any assistance I would greatly appreciate it.

HT - Days Worked =

VAR startdate= SELECTEDVALUE('data'[Target Start Date])

VAR enddate = ENDOFMONTH(Dates[Date])

VAR startmonth = STARTOFMONTH(Dates[Date])

RETURN DATEDIFF(startdate, enddate,DAY) + 1

Here is a sample of my data:

Name	Start Date
Jane Doe	2022-01-15
John Dow	2022-02-01
Jane Smith	2022-01-01
John Smith	2022-02-01

Name	Start Date
Jane Doe	2022-01-15
John Dow	2022-02-01
Jane Smith	2022-01-01
John Smith	2022-02-01

[v-janeyg-msft · Accepted solution]

Hi, @Anonymous 

According to your description, I create a measure, when the selected month is less than the start date, the beginning and end of the selected date are used for comparison. You can also modify according to your ideas.

Like this:

Measure =
VAR beginmonth =
    DATE ( YEAR ( MIN ( 'Date'[Date] ) ), MONTH ( MIN ( 'Date'[Date] ) ), 1 )
VAR selectenddate =
    EOMONTH ( beginmonth, 0 )
VAR startdate =
    IF (
        SELECTEDVALUE ( 'Table'[Start Date] ) > selectenddate,
        beginmonth,
        SELECTEDVALUE ( 'Table'[Start Date] )
    )
RETURN
    DATEDIFF ( startdate, selectenddate, DAY ) + 1

Did I answer your question? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.

Best Regards,
Community Support Team _ Janey

[Anonymous]

Hi Janey, Yes this answers my question. Thank you so much!

[VahidDM]

Hi @Anonymous 

Can you share a sample of your data in a text format?

BTW, you need to add a date table to your report, then filter that date table with start and end date, and count the dates between those start and finish.

Add Date Table:

https://www.vahiddm.com/post/creating-calendar-table-with-3-steps

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/