How many days in each month

[Anonymous]

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

[Anonymous]

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?

[Anonymous]

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

[Anonymous]

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.

[CNENFRNL]

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!

[Anonymous]

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

[CNENFRNL · Accepted solution]

@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"

[Anonymous]

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

[Anonymous]

@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!

[CNENFRNL]

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

[amitchandak]

@Anonymous , Try if this file can help

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