Hi everyone,

I want to create a column in the matrix table to show the count of OK for previous month, I named the measure as Prev Month OK count as shown in the screenshot above. However, the output is not what I want based on what I tried. This is my formula:

Prev Month OK count = CALCULATE([OK count], DATEADD('Aggregate'[Intake],-1,MONTH))

Even I change the MONTH parameter to QUARTER in DATEADD, it doesn't work as well, there is only a value in the Total which I'm not sure what does the value means.

The screenshot above is the expected output that I want. Take note that [OK count] is also a measure.

Any help or advise will be greatly appreciated!

Screenshot for Date Column

CodePudding user response：

You can do it in Power Query and create a new table using this following code-

let your table name is your_old_table_name

let you have these 2 column there - date and value

Now create a new table **your_new_table_name" as below-

let
    Source = your_old_table_name,
    #"Grouped Rows" = Table.Group(Source, {"date"}, {{"_sum", each List.Sum([value]), type nullable number}}),
    #"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"date", Order.Ascending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each [Index]   1),
    #"Merged Queries" = Table.NestedJoin(#"Added Custom", {"Index"}, #"Added Custom", {"Custom"}, "Added Custom", JoinKind.LeftOuter),
    #"Expanded Added Custom" = Table.ExpandTableColumn(#"Merged Queries", "Added Custom", {"_sum"}, {"Added Custom._sum"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Added Custom",{"Index", "Custom"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Added Custom._sum", "previous_date_sum"}})
in
    #"Renamed Columns"

You will have an output similar as below-