Getting Data bewteen range of dates

[Shalva]

Hello

I have a table of workers with dates

Name	Start Date in the company	End Date in the company
A	15/03/20	31/03/20
B	01/03/20	31/04/20
C	10/03/20	10/05/20

And so on...

I want to alculate and show the amount of wages that have been paid each month

Name	March	April	May
A	0.5	0	0
B	1	1	0
C	0.6	1	0.3

I want to find the best and efficent way to do it because I have a lot of workers and can't make 12 rows for each month for each individual.

I tried to make a measure for each month but it didn't work

MeasureMarch=

var StartMonth = Max( Table[Start Date], Date(2020,03,01))

var EndMonth = Min( Date(2020,03,31), Table[End Date])

var Wage=Calculate((NextMonth-ThisMonth)/30)

Flag = And( Month(StartMonth)=3, Month(EndMonth=3)), 

return If( Flag, Wage, 0)

Any thoughts?

[CNENFRNL · Accepted solution]

@Shalva , both DAX and Power Query do the trick.

DAX solution

PQ solution,

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI01Tcw1jcyADKNDaHMWJ1oJSeggIEhQs5A38AEJucM0mcAlwMxTcFysQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, #"Start Date in the company" = _t, #"End Date in the company" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Start Date in the company", type date}, {"End Date in the company", type date}}, "Fr"),
    #"Added Custom" = Table.AddColumn(
        #"Changed Type",
        "Custom",
        each List.Accumulate(
            List.Dates([Start Date in the company], Duration.Days([End Date in the company]-[Start Date in the company])+1,#duration(1,0,0,0)),
            [],
            (s,c) => let yyyyMM = Date.ToText(c, "yyyyMM"), value = Record.FieldOrDefault(s, yyyyMM, "NA") in if value = "NA" then Record.AddField(s, yyyyMM, 1) else s & Record.AddField([], yyyyMM, value+1)
        )
    ),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Start Date in the company", "End Date in the company"}),
    Custom1 = Table.TransformColumns(
        #"Removed Columns",
        {{"Custom",
            each List.Accumulate(
                Record.FieldNames(_),
                [],
                (s,c) => Record.AddField(s, c, Number.Round(Record.Field(_,c)/Date.DaysInMonth(Date.From(c&"01")),2))
            )
        }}
    ),
    #"Expanded Custom" = Table.ExpandRecordColumn(Custom1, "Custom", {"202003", "202004", "202005"}, {"202003", "202004", "202005"})
in
    #"Expanded Custom"