Adding 60 onto each day value

[Kassii]

Hi guys. Just wondering if someone could give me a hand

Currently I need to have a comulative value of 60 being added onto each day per month (Being reset back to 60 at the start of the new month)

So on the 1st the value is 60, second it's 120, 3rd its 180 and so on. However there are also days where the place is closed and therefore 60 doesn't need to be added.

I am currently racking my brain and have no idea how exactly I am meant to do this

If anyone could help me that would be great

Many thanks

[v-huizhn-msft]

Hi @Kassii,

Have you resolved your issue? Please share your solution or mark the helpful reply as answer if you have resoved it. More people will find workaround easily and quickly.

Thanks,
Angelia

[v-huizhn-msft]

Hi @Kassii,

Please use the Query @MarcelBeug posted,  which is same in Power BI. Just replace the table name and column name to yours. Then share your sample table model and list expected result if you still have problem needed to solve. So that we can share the detailed solution which is close to your requirement.

Thanks,
Angelia

[MarcelBeug · Accepted solution]

Some fake sample data would have helped us understand your case.

In the screen shot below, the blue table is the input in Table1.

The green table is the table from query Result.

Query Table1 imports Table1 into Power Query.

Query Result:

let
    Source = Table1,
    #"Inserted End of Month" = Table.AddColumn(Source, "End of Month", each Date.EndOfMonth([Date]), type date),
    #"Filtered Rows" = Table.SelectRows(#"Inserted End of Month", each ([OpenOrClosed] = "Open")),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"End of Month"}, {{"AllData", each Table.AddIndexColumn(_,"Value",60,60), Value.Type(Table.AddColumn(#"Filtered Rows","Value",each 0, Int64.Type))}}),
    #"Expanded AllData" = Table.ExpandTableColumn(#"Grouped Rows", "AllData", {"Date", "OpenOrClosed", "Value"}, {"Date", "OpenOrClosed", "Value"}),
    #"Merged Queries" = Table.NestedJoin(Source,{"Date"},#"Expanded AllData",{"Date"},"AllData",JoinKind.LeftOuter),
    #"Expanded AllData1" = Table.ExpandTableColumn(#"Merged Queries", "AllData", {"Value"}, {"Value"}),
    #"Replaced Value" = Table.ReplaceValue(#"Expanded AllData1",null, each if [Date] = Date.StartOfMonth([Date]) then 0 else null,Replacer.ReplaceValue,{"Value"}),
    #"Restored Type" = Value.ReplaceType(#"Replaced Value",Value.Type(#"Expanded AllData1")),
    #"Sorted Rows" = Table.Sort(#"Restored Type",{{"Date", Order.Ascending}}),
    #"Filled Down" = Table.FillDown(#"Sorted Rows",{"Value"})
in
    #"Filled Down"