How can I add Area from start Date and end date using dax to use in Line chart

[ihaqqani]

I want to add the monthly occupied Area based on my Start Date and End Date.  The challenge here is Start date and end date could be one month only like January to January.  It could be Jan to March, so in this case,  area occupied should go to January , February and March month respectively. 

It can also be March to June, in this scenario Area must go to all  
four months, (March, April, May , June).

It needs another additional filter,  filter by Cubic.

I have attached the sample of data and expected output.

I hope this is clear .

Any help would be much appreciated. 

[Ahmedx]

Sample PBIX file attached
https://1drv.ms/u/s!AiUZ0Ws7G26Rhh62FgbYW72YEMhJ?e=Q6Icak
and if you have many years in the report, then it’s better to download this:
https://1drv.ms/u/s!AiUZ0Ws7G26Rhh-T1PZ2gldnuRk0?e=X1g6iG
1) Creat Calendar table

2) create a calculated column

2) create a bridge-table

3) create a relationship between tables

[ihaqqani]

Ahmed.

Thanks a ton,  this is what I was looking for.

Keep the good work up.

[Ahmedx · Accepted solution]

I don’t know, but my monthly sum will look like this:

[Ahmedx]

POWER QUERY +DAX
see attached
https://1drv.ms/u/s!AiUZ0Ws7G26RhhlO-wjKruWQUAnL?e=gOfoL5

Area Occupied = SUM('Table'[Area Occupied in Cu.M Q])

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lZLNCsIwDMffpedakjTrx1EEwYH4AGOHbewwkB3E+fx2U6TFDW0v+Wjza0j+VSVYSKHJQTCHqR26YC/XXdmMO9LBB1TBVwRLpJNo392HRy9q+aaAlhRhyDJ4ku6LQk4d+3aDgoWTHFGQNXotTV4vuCQ/jHA8rRHOzW2LYE1MKDwC/99DsVwSyhhiDOQRiN1aedS0BlVOa+XzvxY5GaXhwqHNGeX8lBCTtaIPW+WVtf4QB8QSI4v0ygRGJIZUGqexSSmUQNCBy2DUTw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Warehouse NumbeQ" = _t, #"Area Occupied in Cu.M Q" = _t, #"Unit Q" = _t, #"Rent Q" = _t, #"From Q" = _t, #"то D" = _t, ActiveQ = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Warehouse NumbeQ", Int64.Type}, {"Area Occupied in Cu.M Q", type number}, {"Unit Q", type text}, {"Rent Q", type text}, {"From Q", type date}, {"то D", type date}, {"ActiveQ", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Month", each List.Transform(
{Date.Month([From Q])..Date.Month([то D])},Text.From)),
    #"Expanded Month" = Table.ExpandListColumn(#"Added Custom", "Month"),
    #"Added Custom1" = Table.AddColumn(#"Expanded Month", "Date", each Date.From("01."&[Month]&"." & Text.From(Date.Year([то D])))),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom1",{{"Date", type date}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Month"})
in
    #"Removed Columns"

[ihaqqani]

Thanks for this, unfortunately i have no idea how to use this, is it possible to send pbix file .?, alternately how can I implement this in my code?

[ihaqqani]

That is correct  , this is what I am looking for , you did this with DAX, or just calculation. 

[amitchandak]

@ihaqqani , seem like you need something similar

Power BI: HR Analytics - Employees as on Date : https://youtu.be/e6Y-l_JtCq4
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

refer to the attached file, if needed

[ihaqqani]

Hi Amit,

Thanks for the reply.

Is it possibly for you to  just write the dax I require , there are so many pieces of codes in the report. it looks this is not something i am looking for.