topic Re: How many days in each month in DAX Commands and Tips

How many days in each month

Anonymous — Tue, 22 Sep 2020 15:53:33 GMT

Hi guys!

I would like to have one (ore more) measure that allows me to calculate how many days are between dates but in each month.

Let's see the example:

Start date = 14/08/2020

End Date = 03/09/2020

I need to know how many days are from August (month1) and how many days are from September (month2)

The idea is to calculate the number o Room Nights in a hotel stay, by month

In this example i would get the results :

August =18 (31/08/2020 - 14-08/2020+1)

September =3 (03/09/2020-01/09/2020+1)

Is it possible ?

Thanks in advance

Re: How many days in each month

amitchandak — Tue, 22 Sep 2020 16:30:23 GMT

Anonymous , Try if this file can help

https://www.dropbox.com/s/bqbei7b8qbq5xez/leavebetweendates.pbix?dl=0

Re: How many days in each month

CNENFRNL — Tue, 22 Sep 2020 21:13:32 GMT

Hi, Anonymous , why bother to use DAX? Excel, the good old one, works perfectly; thanks to the intrinsic speciality of FREQUENCY func, it does the trick without any sweat. 😁

I attach an Excel file for your reference. Have fun!

Re: How many days in each month

Anonymous — Wed, 23 Sep 2020 08:04:03 GMT

A measure can return just one value. How do you want to return multiple values if the start and end dates are in different months?

Re: How many days in each month

Anonymous — Wed, 23 Sep 2020 08:14:28 GMT

Thank you CNENFRNL but i need those values to complement a report with other values 🙂

Re: How many days in each month

Anonymous — Wed, 23 Sep 2020 08:15:57 GMT

Thank you Anonymous , thats why i wrote one (or more) measures

Re: How many days in each month

Anonymous — Wed, 23 Sep 2020 10:19:19 GMT

Hm... One measure would be more than enough. You should write just one measure that returns the number of days between any two dates and then do the rest by suitable slicing of the Date dimension. Proliferation of measures is never a good thing.

Re: How many days in each month

CNENFRNL — Wed, 23 Sep 2020 10:43:07 GMT

Anonymous, I came up with a Power Query solution, so that all data could be directly consumed by your reports, I hope.

M codes are as follows, have fun once more! 😁

let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIyMDLQNbDQNTSBcyx1DYyVYnWilZzgQua6RijyFmB5Z7iQGUgIwTEyVYqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Custome = _t, #"Start Date" = _t, #"End Date" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Custome", type text}, {"Start Date", type date}, {"End Date", type date}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Generate( () => [ s = [Start Date], cm = Date.ToText(s, "yyyy/MM"), stay = if [End Date] <= Date.EndOfMonth([Start Date]) then Duration.Days([End Date] - [Start Date]) + 1 else Duration.Days(Date.EndOfMonth([Start Date]) - [Start Date]) + 1 ], (condition) => condition[s] <= [End Date], (step) => [ s = Date.AddDays(Date.EndOfMonth(step[s]), 1), cm = Date.ToText(s, "yyyy/MM"), stay = if [End Date] <= Date.EndOfMonth(s) then Duration.Days([End Date] - s) + 1 else Date.DaysInMonth(s) ], each [yyyymm = [cm], Stay = [stay]] ) ), #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"), #"Expanded Custom1" = Table.ExpandRecordColumn(#"Expanded Custom", "Custom", {"yyyymm", "Stay"}, {"Year/Month", "Stay"}) in #"Expanded Custom1"

Re: How many days in each month

Anonymous — Wed, 23 Sep 2020 10:49:45 GMT

CNENFRNL i think this could be the solution, bur I'm not very familiar with "M"... Could you send me the .pbix file just to try to understand? Thank you so much!

Re: How many days in each month

CNENFRNL — Wed, 23 Sep 2020 11:54:42 GMT

Hi, Anonymous , I attach a pbix file for your reference.

Re: How many days in each month

Anonymous — Fri, 25 Sep 2020 17:45:05 GMT

Thank you so much CNENFRNL ! i just get it work with your solution! Works great !💪