Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
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

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

vjaneygmsft_0-1649337965243.png

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

 

View solution in original post

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:

NameStart Date
Jane Doe2022-01-15
John Dow2022-02-01
Jane Smith2022-01-01
John Smith2022-02-01

 

NameStart Date
Jane Doe2022-01-15
John Dow2022-02-01
Jane Smith2022-01-01
John Smith2022-02-01

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

vjaneygmsft_0-1649337965243.png

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

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

VahidDM
Super User
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.

 

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors