Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Biju
Helper I
Helper I

How to Plot continous bar chart even if a month does not have value(need to show previous months)

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.

 

pbi.PNG

 

Appreaciated your help in advance.

14 REPLIES 14
Hot_Potato
Frequent Visitor

Hi were you able to solve this? @Biju 

v-huizhn-msft
Employee
Employee

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.

1.PNG

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.

3.png

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

@v-huizhn-msft,

 

Its not solved yet!

Hi @Biju,

The solution is not helpful? Could you please share your issue?

Best Regards,
Angelia

@v-huizhn-msft

 

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.

1.PNG

2. The date is uncontinous, so you need to create another table including continous date like screenshot below.

2.PNG

3. Create a relationship between the two tables.

3.png

 

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.PNG

5. Then create a clustered column visual, select DateTable[Date] as x-asix, the [result] as value, you will get expected result.

6.PNG

Best Regards,
Angelia

@v-huizhn-msft,

 

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())) 

tab.PNG

 

 

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)

 

wid.PNG

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

 

sourcePNG.PNG

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,

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.