cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Anonymous
Not applicable

## # 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, @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
``````

Best Regards,
Community Support Team _ Janey

4 REPLIES 4
Anonymous
Not applicable

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, @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
``````

Best Regards,
Community Support Team _ Janey

Anonymous
Not applicable

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

Super User

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.

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.

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors