Spreading monthly data by category across daily dates by category

[hbusche]

I have been trying for too long to figure this out.

This is my monthly data 

This is my calendar table ( I couldn't figure out how to get it to give me the dates for September as well)

I need a column or measure that will spread my monthly data evenly over my daily dates per propnum for S370 and S371

I was able to get close using this measure in the calendar table, but it doesn't do it by propnum, and only for S370. 

Daliy Value Measure = 
VAR DaysofMonth =
    DAY ( EOMONTH ( MAX ( 'Calendar'[Date] ), 0 ) )
RETURN
    CALCULATE (
        SUM ( 'FACT TABLE'[S370] ),
        FILTER (
            ALL ( 'Fact Table' ),
            'FACT TABLE'[OUTDATE]
                >= EOMONTH ( MAX ( 'Calendar'[Date] ), -1 ) + 1
                && 'FACT TABLE'[OUTDATE] <= EOMONTH ( MAX ( 'Calendar'[Date] ), 0 )
        )
    ) / DaysofMonth

[AllisonKennedy]

@hbusche Do you have a proper DimDate table? https://excelwithallison.blogspot.com/2020/04/dimdate-what-why-and-how.html

Also, do you have a DimPropNum table per chance? https://excelwithallison.blogspot.com/search?q=it%27s+complicated 

[hbusche]

@AllisonKennedy I have the monthly table, I need to create the daily table. Here I am only showing propnum1 in the daily table and only the first month and a few days, but I think the idea is clear that I would need a complete table with Propnum2 and 3 and associated dates under propnum1.

[Anonymous · Accepted solution]

Hi @hbusche ,

I created a sample pbix file(see attachment), please check whether that is what you want. You can copy the following applied codes in your Advanced Editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bdBBCgMhDAXQu7geMD8xqZdoOxS6Gub+12gitGimC0Xw+ZN4HGV/PffH+46yFVRBZeI4sy8r57YArty/oPuSnoVMEWa+6YW0KvQLuUXZ1XCKUaJ4pdnMOWh+/w/pFITWxxCel5TNUdJbKCxNSZoePJRCkdncuzMazHViS/tQHX+FC1sGII2iRuS9nR8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [PROPNUM = _t, OUTDATE = _t, S370 = _t, S371 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"PROPNUM", type text}, {"OUTDATE", type date}, {"S370", Int64.Type}, {"S371", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "D_Date", each List.Dates(Date.StartOfMonth([OUTDATE]), Number.From([OUTDATE]-Date.StartOfMonth([OUTDATE]))+1, #duration(1, 0, 0, 0))),
    #"Expanded D_Date" = Table.ExpandListColumn(#"Added Custom", "D_Date"),
    #"Added Custom1" = Table.AddColumn(#"Expanded D_Date", "Daily S370", each [S370]/(Number.From([OUTDATE]-Date.StartOfMonth([OUTDATE]))+1)),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Daily S371", each [S371]/(Number.From([OUTDATE]-Date.StartOfMonth([OUTDATE]))+1)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"OUTDATE", "S370", "S371"})
in
    #"Removed Columns"let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bdBBCgMhDAXQu7geMD8xqZdoOxS6Gub+12gitGimC0Xw+ZN4HGV/PffH+46yFVRBZeI4sy8r57YArty/oPuSnoVMEWa+6YW0KvQLuUXZ1XCKUaJ4pdnMOWh+/w/pFITWxxCel5TNUdJbKCxNSZoePJRCkdncuzMazHViS/tQHX+FC1sGII2iRuS9nR8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [PROPNUM = _t, OUTDATE = _t, S370 = _t, S371 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"PROPNUM", type text}, {"OUTDATE", type date}, {"S370", Int64.Type}, {"S371", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "D_Date", each List.Dates(Date.StartOfMonth([OUTDATE]), Number.From([OUTDATE]-Date.StartOfMonth([OUTDATE]))+1, #duration(1, 0, 0, 0))),
    #"Expanded D_Date" = Table.ExpandListColumn(#"Added Custom", "D_Date"),
    #"Added Custom1" = Table.AddColumn(#"Expanded D_Date", "Daily S370", each [S370]/(Number.From([OUTDATE]-Date.StartOfMonth([OUTDATE]))+1)),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Daily S371", each [S371]/(Number.From([OUTDATE]-Date.StartOfMonth([OUTDATE]))+1)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"OUTDATE", "S370", "S371"})
in
    #"Removed Columns"

Best Regards

[hbusche]

@Anonymous Thank you very much for providing the steps and the file. I was able to follow what you did, and learned a lot in the process and was able to apply it to my larger file. Thank you!

[hbusche]

@AllisonKennedy I do have a proper date table in the file, as well as a propnum table.  I couldn't get it to work using the full date table and was getting confused. I found a suggestion in this forum that got me to where I am with a simpler calendar table, and now I am stuck again as the example didn't show how to do it with different categories, or for me, propnums. I am happy to try something different using the full date table I have.

I have monthly data (different number of months and starting and stopping months for each, though all are end of month dates) for a large number of different propnums that I need to make daily data. That is the goal I am not getting to now.