cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## How to create dummy columns in numeric order based on a date difference

Hi All,

I have created a report using the matrix visual. The column is a date difference in month. 1, 2, 3.... Row is a date value.
There are some months "missing" purely due to what the data is, however it need to show those months just with a zero value.

So if its month 1 2 3 7 - I need to fill in 4 5 6, etc. Is there anyway of doing that without adding dummy data in the actual data.

Jerusha

9 REPLIES 9
Frequent Visitor

Hi,

Can you guide me how we can move sub columns total to the left side of matrix?

Helper I

Hi All,

I am working through the solution provider.. not sure if I am on the right track

I created a new table with the number of months required.

I joined this to my existing table

and this is what I get. Almost as an inner join?

Also when I join this to my data, which field do I use to the matrix to show the months 1 - 47 in this case.

THank you all again 😊
Jerusha

Community Support

Hi @Jerush ,

Could you tell me if your problem has been solved?
If it is, kindly Accept it as the solution. More people will benefit from it.

Best Regards,
Stephen Tao

Helper I

Hi @v-stephen-msft
Hope you are well. Please see the new info I have provided.

Thank you

Jerusha

Helper I

Hi Stephan,

THis solution looks like it will work. I am just working through the steps that has been given.  Still at a beginner level on power bi.

Thank you though.

Helper I

Create a new table and add relationship;

Months = SELECTCOLUMNS(GENERATESERIES(1,12,1),"MonthNo", [Value])

Add this new column to matrix.

Then change properties of  measure in visualisation "Show Item with no data" or change your measures to display 0 instedof BLANK()

Helper I

Hi Msta,

Thank you for the speedy reply. I will give this a go. Much Appreciated

Super User

@Jerush , Create a table with values , using generateseries

month= generateseries(1,12,1)

Join this with the column in your table.

Create a measure with +0 and use

count(Table[column]) +0

Helper I

Thank you for the speedy reply. I will give this a go.

Much Appreciated.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

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

Top Solution Authors
Top Kudoed Authors