Issue in month months dynamically

[GokilaRaviraj]

Hi,

Currently my logic is to show values from current month + next 11 months

for this i used a M query below:

currentMonth = List.Generate(()=>Date.Month(Date.From(DateTime.FixedLocalNow()))-1, each _ < Date.Month(Date.From(DateTime.FixedLocalNow()))+11, each _+1, each Date.MonthName(#date(1899,Number.Mod(_, 12)+1,1))))

Now the logic is going to be like current month + remaining months of the year + next year all the months

below is the complete m query i used.

let
Source = Excel.Workbook(File.Contents("C:\Users\Gokila\OneDrive - Ledstången\Documents\Reports to load in BI\Rent_Roll_Updated.xlsx"), null, true),
#" Pure Role_Sheet" = Source{[Item=" Pure Role",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(#" Pure Role_Sheet", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Company", type text}, {"Property designation", type text}, {"Building", type text}, {"Agreement number", type text}, {"Tenant (Name)", type text}, {"Tenant Person/Organization no", Int64.Type}, {"Street (Avi)", type text}, {"Postcode (Avi)", Int64.Type}, {"Postal address (Avi)", type text}, {"C/O (Avi)", type text}, {"Customer's reference", type text}, {"Delivery method", type text}, {"Email (Avi)", type text}, {"Bank for private e-invoice", type text}, {"Intermediary", type text}, {"GLN/Peppol", Int64.Type}, {"Direct debit (Yes/No)", type logical}, {"Direct debit provider", type any}, {"Payer number", Int64.Type}, {"AG Clearing", type any}, {"AG account number", type any}, {"Other tenant (Name)", type text}, {"Other tenant Person/organisation no", type text}, {"Contract type", type text}, {"Main agreement", type any}, {"Status", type text}, {"Vacant (Yes/No)", type text}, {"Notification status", type text}, {"Start date", type date}, {"End date", type date}, {"Extended to", type date}, {"Date of extension", type date}, {"Notice period (Mon)", Int64.Type}, {"Extension period (months)", Int64.Type}, {"Warning period", Int64.Type}, {"Termination date", type date}, {"Termination type", type text}, {"Move-out date", type date}, {"Terminated by", type text}, {"Object number", type text}, {"Lgh number", type text}, {"Object type", type text}, {"Object category", type text}, {"Area (m2)", type number}, {"Street (Object)", type text}, {"Postcode (Object)", type text}, {"Postal address (Object)", type text}, {"Period", type text}, {"Deposit", Int64.Type}, {"Basic rent", type number}, {"Base rent (incl. index)", type number}, {"Base rent ((incl. index). (kr/m2))", type number}, {"VAT base rent", type number}, {"Base month", Int64.Type}, {"Index base year", Int64.Type}, {"Regulation percentage", type text}, {"Enumeration month", Int64.Type}, {"Minimum units of change", type text}, {"Allow lowering (Yes/No)", type text}, {"Percentage increase", type text}, {"Minimum Adjustment (%)", type text}, {"Maximum Adjustment (%)", type text}, {"Index amount", type number}, {"Share of property tax (%)", Int64.Type}, {"Taxation type", type text}, {"Property tax", Int64.Type}, {"Multiple Indexed Rows (Yes/No)", type text}, {"IMD (Yes/No)", type any}, {"Electricity", Int64.Type}, {"Electricity (incl. index)", Int64.Type}, {"Water", type number}, {"Water (incl. index)", type number}, {"Hot water", Int64.Type}, {"Hot water (incl. index)", Int64.Type}, {"Cold", Int64.Type}, {"Cooling (incl. index)", Int64.Type}, {"HEAT", Int64.Type}, {"Heat (incl. index)", Int64.Type}, {"Parking", Int64.Type}, {"Parking (incl. index)", Int64.Type}, {"Gas", Int64.Type}, {"Gas (incl. index)", Int64.Type}, {"Water and sewage", Int64.Type}, {"Water and sewage (incl. index)", Int64.Type}, {"Sign", Int64.Type}, {"Sign (incl. index)", Int64.Type}, {"Operation/Maintenance", type number}, {"Operation/Maintenance (incl. index)", type number}, {"Internet/TV", Int64.Type}, {"Internet/TV (incl. index)", Int64.Type}, {"Snow removal", Int64.Type}, {"Snow removal (incl. index)", Int64.Type}, {"Waste disposal", Int64.Type}, {"Waste management (incl. index)", Int64.Type}, {"Cleaning/Cleaning", Int64.Type}, {"Cleaning/Cleaning (incl. index)", Int64.Type}, {"Ventilation", Int64.Type}, {"Ventilation (incl. index)", Int64.Type}, {"Remodeling/tenant adaptation", Int64.Type}, {"Reconstruction/tenant adaptation (incl. index)", Int64.Type}, {"Case article", Int64.Type}, {"Case article (incl. index)", Int64.Type}, {"Other additions", type number}, {"Other supplements (incl. index)", type number}, {"Total additions (incl. index)", type number}, {"Discount year 1 (incl. index)", Int64.Type}, {"Discount year 2 (incl. index)", Int64.Type}, {"Discount year 3 (incl. index)", Int64.Type}, {"Annual discount >= year 4", Int64.Type}, {"Total annual amount", type number}, {"Rental value Contract", type number}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{ "C/O (Avi)", "Customer's reference", "Intermediary", "GLN/Peppol", "Direct debit provider", "AG Clearing", "Payer number", "Other tenant (Name)", "AG account number"}),
#"Grouped Rows" = Table.Group(#"Removed Columns", {"Company","Period","Object number"}, {{"Sum of Annual Amount", each List.Sum([Total annual amount]), type nullable number}}),


#"Added Custom2" = Table.AddColumn(#"Grouped Rows", "Rental Income", each if [Period]="Per quarter" then let amount =[Sum of Annual Amount]/4 in List.Repeat({amount}, 12)
else if [Period]="Per month" then let amount = [Sum of Annual Amount]/12 in List.Repeat({amount}, 12)
else if [Period]="A year" then let amount =[Sum of Annual Amount] in List.Repeat({amount}, 12)
else let amount = [Sum of Annual Amount]/4 in List.Repeat({amount}, 12)),

currentMonth = List.Generate(()=>Date.Month(Date.From(DateTime.FixedLocalNow()))-1, each _ < Date.Month(Date.From(DateTime.FixedLocalNow()))+11, each _+1, each Date.MonthName(#date(1899,Number.Mod(_, 12)+1,1)))),

Custom1 = Table.AddColumn(#"Added Custom2", "months", each currentMonth),

#"Added Custom1" = Table.AddColumn(Custom1, "Custom", each Table.FromRows(List.Zip({[months], [Rental Income]}))),
#"Removed Columns1" = Table.RemoveColumns(#"Added Custom1",{"months", "Rental Income"}),

Custom2 = Table.TransformColumns(#"Removed Columns1", {{"Custom", each Table.PromoteHeaders(Table.Transpose(_))}}),

#"Expanded Custom" = Table.ExpandTableColumn(Custom2, "Custom", currentMonth),

#"Filtered Rows" = Table.SelectRows(#"Expanded Custom", each true),

#"Changed Type1" = Table.TransformColumnTypes(#"Filtered Rows",{{"augusti", type number}, {"september", type number}, {"oktober", type number}, {"november", type number}, {"december", type number}, {"januari", type number},
{"februari", type number}, {"mars", type number}, {"april", type number}, {"maj", type number}, {"juni", type number}, {"juli", type number}}),
#"Added Custom" = Table.AddColumn(#"Changed Type1", "Rental Income", each [augusti]+[september]+[oktober]+[november]+[december]+[januari]+[februari]+[mars]+[april]+[maj]+[juni]+[juli]),
#"Added Conditional Column" = Table.AddColumn(#"Added Custom", "August", each if [Period] = "Per quarter" then 0 else if [Period] = "A year" then 0 else [augusti]),
#"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "September", each if [Period] = "Per quarter" then [september] else if [Period] = "A year" then 0 else [september]),
#"Added Conditional Column2" = Table.AddColumn(#"Added Conditional Column1", "October", each if [Period] = "Per quarter" then 0 else if [Period] = "A year" then 0 else [oktober]),
#"Added Conditional Column3" = Table.AddColumn(#"Added Conditional Column2", "November", each if [Period] = "Per quarter" then 0 else if [Period] = "A year" then 0 else [november]),
#"Added Conditional Column4" = Table.AddColumn(#"Added Conditional Column3", "December", each if [Period] = "Per quarter" then [december] else if [Period] = "A year" then 0 else [december]),
#"Added Conditional Column5" = Table.AddColumn(#"Added Conditional Column4", "January", each if [Period] = "Per quarter" then 0 else if [Period] = "A year" then 0 else [januari]),
#"Added Conditional Column6" = Table.AddColumn(#"Added Conditional Column5", "February", each if [Period] = "Per quarter" then 0 else if [Period] = "A year" then 0 else [februari]),
#"Added Conditional Column7" = Table.AddColumn(#"Added Conditional Column6", "March", each if [Period] = "Per quarter" then [mars] else if [Period] = "A year" then 0 else [mars]),
#"Added Conditional Column8" = Table.AddColumn(#"Added Conditional Column7", "April", each if [Period] = "Per quarter" then 0 else if [Period] = "A year" then 0 else [april]),
#"Added Conditional Column9" = Table.AddColumn(#"Added Conditional Column8", "May", each if [Period] = "Per quarter" then 0 else if [Period] = "A year" then 0 else [maj]),
#"Added Conditional Column10" = Table.AddColumn(#"Added Conditional Column9", "June", each if [Period] = "A year" then [juni] else if [Period] = "Per quarter" then [juni] else [juni]),
#"Added Conditional Column11" = Table.AddColumn(#"Added Conditional Column10", "July", each if [Period] = "Per quarter" then 0 else if [Period] = "A year" then 0 else [juli]),
#"Renamed Columns" = Table.RenameColumns(#"Added Conditional Column11",{{"september", "September2"}}),

#"Removed Columns2" = Table.RemoveColumns(#"Renamed Columns",{"augusti", "September2", "oktober", "november", "december", "januari", "februari", "mars", "april", "maj", "juni", "juli"}),
#"Grouped Rows1" = Table.Group(#"Removed Columns2", {"Company", "Period","Object number"}, {{"SeptemberSum", each List.Sum([September]), type number}, {"OctoberSum", each List.Sum([October]), type number}, {"NovemberSum", each List.Sum([November]), type number}, {"DecemberSum", each List.Sum([December]), type number}, {"JanuarySum", each List.Sum([January]), type number}, {"FebruarySum", each List.Sum([February]), type number}, {"MarchSum", each List.Sum([March]), type number}, {"AprilSum", each List.Sum([April]), type number}, {"MaySum", each List.Sum([May]), type number}, {"JunSum", each List.Sum([June]), type number}, {"JulSum", each List.Sum([July]), type number}, {"AugustSum", each List.Sum([August]), type number}, {"Annual Amount", each List.Sum([Sum of Annual Amount]), type nullable number}}),
#"Renamed Columns1" = Table.RenameColumns(#"Grouped Rows1",{{"SeptemberSum", "September"}, {"OctoberSum", "October"}, {"NovemberSum", "November"}, {"DecemberSum", "December"}, {"JanuarySum", "January"}, {"FebruarySum", "February"}, {"MarchSum", "March"}, {"AprilSum", "April"}, {"MaySum", "May"}, {"JunSum", "June"}, {"JulSum", "July"}, {"AugustSum", "August"}}),
#"Cleaned Text" = Table.TransformColumns(#"Renamed Columns1",{{"Company", Text.Clean, type text}}),
#"Trimmed Text" = Table.TransformColumns(#"Cleaned Text",{{"Company", Text.Trim, type text}})
in
#"Trimmed Text"

my output now looks like this.

Could anyone help  me to write a m query ?

[lbendlin]

You provided a lot of code, but not a lot of context.  Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[GokilaRaviraj]

Hi @lbendlin 

Sorry. Please refer the below sample data for your clarification.

Company	Period	Rental income	Sep	Oct	Nov	Dec	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug
A	Month	5000	416.6667	416.6667	416.6667	416.6667	416.6667	416.6667	416.6667	416.6667	416.6667	416.6667	416.6667	416.6667
B	Quarter	5000	1250	1250	1250	1250	1250	1250	1250	1250	1250	1250	1250	1250
B	Month	8000	666.6667	666.6667	666.6667	666.6667	666.6667	666.6667	666.6667	666.6667	666.6667	666.6667	666.6667	666.6667

This is my smaple output. my table report currently looks like this. 
Logic involved here. If Period = Quarter, rental Income /4 , This value will be applicable to the current + 11 months.

If period = month, rental Income /12 , This value will be applicable to the current + 11 months.

Now my new logic is going to be like below table,

C ompany	Period	Rental income	SepFY24	OctFY24	NovFY24	DecFY24	JanFY25	FebFY25	MarFY25	AprFY25	MayFY25	JunFY25	JulFY25	AugFY25	SepFY25	OctFY25	NovFY25	DecFY25
A	Month	5000	416.6667	416.6667	416.6667	416.6667	416.6667	416.6667	416.6667	416.6667	416.6667	416.6667	416.6667	416.6667	416.6667	416.6667	416.6667	416.6667
B	Quarter	5000	1250	1250	1250	1250	1250	1250	1250	1250	1250	1250	1250	1250	1250	1250	1250	1250
B	Month	8000	666.6667	666.6667	666.6667	666.6667	666.6667	666.6667	666.6667	666.6667	666.6667	666.6667	666.6667	666.6667	666.6667	666.6667	666.6667	666.6667

I need to fetch current month + remaining months of the current year + next year all the months. 

These months name must be with the Current year in the headers. for example, Sep FY24.

Points to be noted. I  don't have a date field in my table, so to fetch the months i used this below m query.

currentMonth = List.Generate(()=>Date.Month(Date.From(DateTime.FixedLocalNow()))-1, each _ < Date.Month(Date.From(DateTime.FixedLocalNow()))+11, each _+1, each Date.MonthName(#date(1899,Number.Mod(_, 12)+1,1))))

Can I write a condition in this query like below?

If current month = september then 

currentMonth = List.Generate(()=>Date.Month(Date.From(DateTime.FixedLocalNow()))-1, each _ < Date.Month(Date.From(DateTime.FixedLocalNow()))+15, each _+1, each Date.MonthName(#date(1899,Number.Mod(_, 12)+1,1))))

If current month = October then

currentMonth = List.Generate(()=>Date.Month(Date.From(DateTime.FixedLocalNow()))-1, each _ < Date.Month(Date.From(DateTime.FixedLocalNow()))+14, each _+1, each Date.MonthName(#date(1899,Number.Mod(_, 12)+1,1))))

I tried to do this, But I get error in the If condition saying Current month name wasn't recognized.

Could you help me with your ideas on this?

Thanks in advance.

[lbendlin · Accepted solution]

 I  don't have a date field in my table

Create one.  Usual guidance is to use the first day of the period (first day of the month in your case). Add a proper calendar table to your data model and base your measures on that calendar table.

saying Current month name wasn't recognized.

Spaces are not allowed as part of the variable name