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.
i have a start date and an end date, and i am trying to count how many days in each month, have each period, i use DATEDIFF, but this just give me the days for 1 month
in example, the start date is 04/may
the end date is 04/july
and i want to know how many days have the period in may and in july.
hope someone can help me.
thank you
Hi @Anonymous,
You can try to use following calculated column formula to get detail day count of each year month period.
Detail Count = VAR temp = ADDCOLUMNS ( CALENDAR ( [StartDate], [EndDate] ), "YearMonth", YEAR ( [Date] ) * 100 + MONTH ( [Date] ) ) RETURN CONCATENATEX ( SUMMARIZE ( temp, [YearMonth], "Count", COUNTROWS ( FILTER ( temp, [YearMonth] = EARLIER ( [YearMonth] ) ) ) ), [YearMonth] & ":" & [Count], "|" )
Notice: this column is stored text values, if you want to calculate with other fields, you need to do some additional steps to extract numeric value from text.
Regards,
Xiaoxin Sheng
@Anonymous you have to give internal as "DAY", here is more information about datediff
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
thanks for your reply, but if i just put day, the result is the days in the time period but i need (in the example) the number of days in may and in july, not the total
@Anonymous oh in this case you want to know total number of day in may and in june so which will be 31 days for may and 30 days for june, total 61, is this what you are after?
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
yes, thats what im looking for.
thank you
@Anonymous you can add a column something like this
Total Days = DAY( EOMONTH( Table2[Start], 0 ) ) + DAY( EOMONTH( Table2[End], 0 ) )
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@parry2k thank you, that works for the total days, but didnt give me the days for each month, I am trying creating columns for each month but i dont get to the correct result
@Anonymous add two seperae column.
Days for Start Month = DAY( EOMONTH( Table2[Start], 0 ) ) Days for End Month = DAY( EOMONTH( Table2[End], 0 ) )
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@parry2k it didnt work, the expression didnt allow me to select the date column
@Anonymous not sure why, can you share sample data?
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
94 | |
77 | |
65 | |
53 |
User | Count |
---|---|
145 | |
105 | |
104 | |
90 | |
63 |