Forum Discussion
Helper IIIssue in month months dynamically
I don't have a date field in my tableCreate 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
Super UserYou 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-Forum/ba-p/963216
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/1447523
Helper IIHi 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
I don't have a date field in my tableCreate 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