Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi All,
Im a beginer in Power BI,as im trying to create a bar chart (shows in the figure),Here can see that November does not have any data.My problem is need to use previous months data if any month value is empty.(Here in the place of Nov required data of October).
Please help anyone knows the solution.
Appreaciated your help in advance.
Hi @Biju,
Could you please share your resource data for further analysis? So that I can post the solution which is close to your requirement.
I create the sample data.
You can create a new calculated column using the formula.
New value = IF(ISBLANK(Test1[value]),LOOKUPVALUE(Test1[value],Test1[Month],Test1[Month]-1),Test1[value])
Then create bar chart use the [New value] field.
Best Regards,
Angelia
Hi,
I have applied the same logic but there is no changes in the result.
New = IF(ISBLANK([Rating]),LOOKUPVALUE([Rating],[Created Month Year],[Created Month Year]-1),[Rating])
Hi @Biju,
Have you resolved your problem? If you have, please please mark corresponding reply as answer, other people will benefit from it.
Best Regards,
Angelia
Hi @Biju,
The solution is not helpful? Could you please share your issue?
Best Regards,
Angelia
Hi,
Thanks for your help,but still its having same problem.
In my scenario months are Sept2016,Oct2016,Dec2016,Jan2017,March207 etc.
I dont have a continous month in the table.Here you can see that Nov2016 and Feb2017 is missing.What i need is,if any month is missing need to generate that month and required to use the previous months rating.
Hope this is more understandable.
Once again thank you for your help.
Hi @Biju,
I got what you want. But I want to confirm, if the Jan2017 is missing, you should use Dec2016, or use the previous row value In your resource data the date is from small to big, right? If it is, I try to reproduce your scenario, please review the following steps.
1. I create a sample data table, which is similar with your resource data based on your description.
2. The date is uncontinous, so you need to create another table including continous date like screenshot below.
3. Create a relationship between the two tables.
4. In DataTable, create a index column in Power Query Edit by selecting Index Column(from0,1) under Add column on home page.
Then create two calculated columns using the formulas.
Column = RELATED(Test[Value]) result = IF(ISBLANK(DateTable[Column]),LOOKUPVALUE(DateTable[Column],DateTable[Index],DateTable[Index]-1),DateTable[Column])
5. Then create a clustered column visual, select DateTable[Date] as x-asix, the [result] as value, you will get expected result.
Best Regards,
Angelia
Thank you very much for your support.
I have tried the same but still having issue,please follow the steps.
Created a master calendar (TestDate = CALENDAR(MIN('Skill Matrix'[Created Month Year]),TODAY()))
I could not able to take the rating from skill matrix table to TestDate table.Related() not working.
if i do reverse(relating TestDate to SkillMatrix table) DateColumn = RELATED(TestDate[Date]) this one, unique dates only gettig(date with out data not available)
Thank you very much for your support.
I have tried the same but still having issue,please follow the steps.
Created a master calendar (TestDate = CALENDAR(MIN('Skill Matrix'[Created Month Year]),TODAY()))
I could not able to take the rating from skill matrix table to TestDate table.Related() not working.
if i do reverse(relating TestDate to SkillMatrix table) DateColumn = RELATED(TestDate[Date]) this one, unique dates only gettig(date with out data not available)
Hi @Biju,
[Created Month Year] is day level, or there just is Year/Month? Do you have create a one to one relationship?
Best Regards,
Angelia
Hi @Biju,
Your months are not contionus, please add a rank calculated column using the formula.
Rank=rankx(Table,Table[month],,ASC)
Then create another calculated column based on 'rank' column.
New = IF(AND(ISBLANK([Rating],[month]<>1),LOOKUPVALUE([Rating],[Rank],[Rank]-1),[Rating]) Result=IF([month]=1,LOOKUPVALUE([Rating],[month],12),[new])
Finally add the [Result] column as value level, you will get expected result.
Best Regards,
Angelia
Hi,Kindly note my resource data.
Hi,
My scenario is bit different,
I dont have a contionus months,becuase skill rating are updated occasionally,for example in january its updated next updation in April so the rating of Feb and March should be exactly same of January,
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
122 | |
101 | |
71 | |
61 |