Forum Discussion
Getting MTD and YTD in M query
Most Valuable ProfessionalPlease post a sample input table and output expected.
Hi Vijay_A_Verma ,
Below is the query in which i want to insert MTD , YTD and PYYTD:
let
#"Submission (2)" = let
Source = AnalysisServices.Databases("uat-grs-datatechbi-ssas-tab-uwbi.aws.lmig.com", [TypedMeasureColumns=true, Implementation="2.0"]),
#"Underwriting BI" = Source{[Name="Underwriting BI"]}[Data],
Model1 = #"Underwriting BI"{[Id="Model"]}[Data],
Model2 = Model1{[Id="Model"]}[Data],
#"Added Items" = Cube.Transform(Model2,
{
{Cube.AddAndExpandDimensionColumn, "[Policy Inception Calendar]", {"[Policy Inception Calendar].[UW Month].[UW Month]", "[Policy Inception Calendar].[UW Year].[UW Year]"}, {"Policy Inception Calendar.UW Month", "Policy Inception Calendar.UW Year"}},
{Cube.AddMeasureColumn, "Quoted Premium", "[Measures].[Quoted Premium]"}
}),
#"Filtered Rows" = Table.SelectRows(#"Added Items", each (Cube.AttributeMemberId([Policy Inception Calendar.UW Year]) = "[Policy Inception Calendar].[UW Year].&[2020]" meta [DisplayName = "2020"] or Cube.AttributeMemberId([Policy Inception Calendar.UW Year]) = "[Policy Inception Calendar].[UW Year].&[2021]" meta [DisplayName = "2021"] or Cube.AttributeMemberId([Policy Inception Calendar.UW Year]) = "[Policy Inception Calendar].[UW Year].&[2022]" meta [DisplayName = "2022"])),
#"Added Items1" = Cube.Transform(#"Filtered Rows",
{
{Cube.AddAndExpandDimensionColumn, "[Policy]", {"[Policy].[Policy NAS Profit Center].[Policy NAS Profit Center]"}, {"Policy.Policy NAS Profit Center"}}
}),
#"Filtered Rows1" = Table.SelectRows(#"Added Items1", each (Cube.AttributeMemberId([Policy.Policy NAS Profit Center]) = "[Policy].[Policy NAS Profit Center].&[IronEnviro]" meta [DisplayName = "IronEnviro"])),
#"Added Items2" = Cube.Transform(#"Filtered Rows1",
{
{Cube.AddAndExpandDimensionColumn, "[Policy]", {"[Policy].[New Business Renewal].[New Business Renewal]", "[Policy].[Policy NAS Product].[Policy NAS Product]", "[Policy].[Policy Status].[Policy Status]"}, {"Policy.New Business Renewal", "Policy.Policy NAS Product", "Policy.Policy Status"}}
}),
#"Filtered Rows2" = Table.SelectRows(#"Added Items2", each (Cube.AttributeMemberId([Policy.Policy Status]) = "[Policy].[Policy Status].&[Non-Renew]" meta [DisplayName = "Non-Renew"] or Cube.AttributeMemberId([Policy.Policy Status]) = "[Policy].[Policy Status].&[Quote]" meta [DisplayName = "Quote"] or Cube.AttributeMemberId([Policy.Policy Status]) = "[Policy].[Policy Status].&[REA-Prerenewal]" meta [DisplayName = "REA-Prerenewal"] or Cube.AttributeMemberId([Policy.Policy Status]) = "[Policy].[Policy Status].&[Renewed]" meta [DisplayName = "Renewed"] or Cube.AttributeMemberId([Policy.Policy Status]) = "[Policy].[Policy Status].&[Submitted]" meta [DisplayName = "Submitted"])),
#"Added Items3" = Cube.Transform(#"Filtered Rows2",
{
{Cube.AddAndExpandDimensionColumn, "[Underwriter]", {"[Underwriter].[Underwriter].[Underwriter]", "[Underwriter].[UW Region].[UW Region]"}, {"Underwriter.Underwriter", "Underwriter.UW Region"}}
}),
#"Added Items4" = Cube.Transform(#"Added Items3",
{
{Cube.AddAndExpandDimensionColumn, "[Broker]", {"[Broker].[NAS Broker Branch].[NAS Broker Branch]", "[Broker].[NAS Broker Region].[NAS Broker Region]"}, {"Broker.NAS Broker Branch", "Broker.NAS Broker Region"}}
}),
#"Added Items5" = Cube.Transform(#"Added Items4",
{
{Cube.AddAndExpandDimensionColumn, "[Policy]", {"[Policy].[Source System Name].[Source System Name]"}, {"Policy.Source System Name"}}
}),
#"Added Items6" = Cube.Transform(#"Added Items5",
{
{Cube.AddMeasureColumn, "Total Bound Count", "[Measures].[Total Bound Count]"},
{Cube.AddMeasureColumn, "Total Declined Count", "[Measures].[Total Declined Count]"},
{Cube.AddMeasureColumn, "Total Quote Count", "[Measures].[Total Quote Count]"},
{Cube.AddMeasureColumn, "Total Submission Count", "[Measures].[Total Submission Count]"}
}),
#"Renamed Columns" = Table.RenameColumns(#"Added Items6",{{"Policy Inception Calendar.UW Year", "Year"}, {"Policy Inception Calendar.UW Month", "Month"}}),
#"Replaced Value" = Table.ReplaceValue(#"Renamed Columns","Jan","01",Replacer.ReplaceText,{"Month"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","Feb","02",Replacer.ReplaceText,{"Month"}),
#"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","Mar","03",Replacer.ReplaceText,{"Month"}),
#"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2","Apr","04",Replacer.ReplaceText,{"Month"}),
#"Replaced Value4" = Table.ReplaceValue(#"Replaced Value3","May","05",Replacer.ReplaceText,{"Month"}),
#"Replaced Value5" = Table.ReplaceValue(#"Replaced Value4","Jun","06",Replacer.ReplaceText,{"Month"}),
#"Replaced Value6" = Table.ReplaceValue(#"Replaced Value5","Jul","07",Replacer.ReplaceText,{"Month"}),
#"Replaced Value7" = Table.ReplaceValue(#"Replaced Value6","Aug","08",Replacer.ReplaceText,{"Month"}),
#"Replaced Value8" = Table.ReplaceValue(#"Replaced Value7","Sep","09",Replacer.ReplaceText,{"Month"}),
#"Replaced Value9" = Table.ReplaceValue(#"Replaced Value8","Oct","10",Replacer.ReplaceText,{"Month"}),
#"Replaced Value10" = Table.ReplaceValue(#"Replaced Value9","Nov","11",Replacer.ReplaceText,{"Month"}),
#"Replaced Value11" = Table.ReplaceValue(#"Replaced Value10","Dec","12",Replacer.ReplaceText,{"Month"}),
#"Renamed Columns1" = Table.RenameColumns(#"Replaced Value11",{{"Month", "UW Month"}}),
#"Added Custom" = Table.AddColumn(#"Renamed Columns1", "Month", each Text.Middle([UW Month],7,2)),
#"Renamed Columns2" = Table.RenameColumns(#"Added Custom",{{"Policy.Policy NAS Product", "Product"}, {"Underwriter.Underwriter", "Underwriter"}, {"Broker.NAS Broker Region", "Producer Region"}, {"Broker.NAS Broker Branch", "Producer Branch"}}),
#"Filtered Rows3" = Table.SelectRows(#"Renamed Columns2", each true),
YTD = Date.IsInYearToDate([UW Year]) ,
MTD =
if [UW Month] <= Date.From(DateTime.FixedLocalNow()) and [UW Month]>= Date.StartOfMonth(Date.From(DateTime.FixedLocalNow())) then 1 else 0
in
#"Filtered Rows3",
#"Changed Type" = Table.TransformColumnTypes(#"Submission (2)",{{"Year", Int64.Type}})
in
#"Changed Type"
Thanks
- Vijay_A_Verma
if you can post sample input then we can suggest the steps for MTD and YTD which you can plugin into your code.
as you can see above that its showing all years and all months. What i am looking for is YTD=2022, MTD=08, PYYTD=2021 and PYMTD=082021
THanks
- Vijay_A_Verma
Please see whether you want this. See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VZKxEcQwCAR7UexAIIRQLR7338b7eI+GC0h24GBt3XfTLrtdTd4a09tzHaRvWYyKxlvuUZFh2KhrolQqclRQ/EJXp8HAVo7P22xXJD1vpXzB/eaUJhAY0T+m8mmKEdK81iuC5h5WUWpOrQiapqsiaNqKiqDpfVYEzXmcEqU9x6emOh2WmjppZ2quOEv101xDK0KFrIpGhllF0FQehKZwPDS3UxY0l/SKoOmcla/jPKn/qakZm1h+pc59uRi/7vkB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Year = _t, Month = _t, Amount = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}, {"Month", Int64.Type}, {"Amount", Int64.Type}}), CurYear = Date.Year(DateTime.FixedLocalNow()), CurMonth = Date.Month(DateTime.FixedLocalNow()), YTD = List.Sum(Table.SelectRows(#"Changed Type", each ([Year] = CurYear) and ([Month] <=CurMonth))[Amount]), MTD = List.Sum(Table.SelectRows(#"Changed Type", each ([Year] = CurYear) and ([Month] =CurMonth))[Amount]), PYYTD = List.Sum(Table.SelectRows(#"Changed Type", each ([Year] = CurYear-1) and ([Month] <=CurMonth))[Amount]), PYMTD = List.Sum(Table.SelectRows(#"Changed Type", each ([Year] = CurYear-1) and ([Month] =CurMonth))[Amount]), Result = #table({"Period", "Amount"}, {{"YTD", YTD}, {"MTD", MTD},{"PYYTD", PYYTD}, {"PYMTD", PYMTD}}) in Result