cancel
Showing results for
Search instead for
Did you mean:
Helper II

## # of Days Dynamic

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?

1 ACCEPTED SOLUTION
Community Support

Hi, @Kryssy

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

4 REPLIES 4
Helper II

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
Community Support

Hi, @Kryssy

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

Helper II

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

Super User

Hi @Kryssy

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/

## Helpful resources

Announcements

#### Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Top Solution Authors
Top Kudoed Authors