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

Question

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.&nbsp;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.&nbsp;Thank you all in advance.Jerusha&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;

amitchandak · Answer

Jerush , Create a table with values , using generateseries 
&nbsp;
month= generateseries(1,12,1) 
&nbsp;
Join this with the column in your table.
&nbsp;
Create a measure with +0 and use
count(Table[column]) +0

msta · Answer

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

jerush · Answer

Hi Msta,&nbsp;Thank you for the speedy reply. I will give this a go. Much Appreciated

jerush · Answer

Hi amitchandak&nbsp;&nbsp;Thank you for the speedy reply. I will give this a go.&nbsp;Much Appreciated.&nbsp;

anonymous · Answer

Hi&nbsp;Jerush&nbsp;,
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.Or you are still confused about it, please provide me with more details about your problem.
Best Regards,Stephen Tao

Forum Discussion

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

9 Replies

Related content

Calculated column to assign numeric code to dates

Custom numeric format for column

Different schema into different destination

How to add two different calculated columns of two different table

1. How to add two different calculated columns of two different table