I trying to populate my table or axis with period even though there are gaps

[Anonymous]

Hi

I am trying to populate my table with a date or period even though is no data so my graph runs continuously along its axis.  I came across an artcile below but I seemed to get a mixed results, mainly due on my part most likely.  

xreference:  Show-dates-with-no-data-on-continuous-date-axis

This is a sample graph, you will notice for 2019 Q2 and Q3 are missing, and again for 2020 we are now in Q4 but no axis details.  The actrual raw data is processed through powerquery, what you see here is just one of the 3 data sources, this one is easier to see the gaps on the chart.  All 3 tabels look at a calendar I created, and each table has an "ApplicationDate" which points to the date field on my calendar.  My calendar date range covers early and future dates to capture all data; the 3 main data soures are refreshed periodically.

SampleGraph

This is my sample data: I have a measure that  just simple counts the value  which is then linked to the calenader against the "ApplicationDate" which is how I get the breakdown by Month and Quarter for each data type.  I am maybe needing to cover all date ranges in the calendar and any that are missing from my data soure are populated with a '0' in the "DataValue" column for each table.

Sample Data Type2

ApplicationDate	DataType	DataValue
02/05/2019	Data2	1
29/10/2018	Data2	1
27/09/2019	Data2	1
27/09/2019	Data2	1
03/10/2019	Data2	1
18/11/2019	Data2	1
18/11/2019	Data2	1
08/11/2019	Data2	1
22/10/2019	Data2	1
28/02/2020	Data2	1
28/02/2020	Data2	1
09/04/2020	Data2	1

Or am I over complicating this task?

many thanks

Chris

[Anonymous]

I am trying to populate my table with a date or period even though is no data so my graph runs continuously along its axis.  I came across an artcile below but I seemed to get a mixed results, mainly due on my part most likely.  

xreference:  Show-dates-with-no-data-on-continuous-date-axis

This is a sample graph, you will notice for 2019 Q2 and Q3 are missing, and again for 2020 we are now in Q4 but no axis details.  The actrual raw data is processed through powerquery, what you see here is just one of the 3 data sources, this one is easier to see the gaps on the chart.  All 3 tabels look at a calendar I created, and each table has an "ApplicationDate" which points to the date field on my calendar.  My calendar date range covers early and future dates to capture all data; the 3 main data soures are refreshed periodically.

SampleGraph

This is my sample data: I have a measure that  just simple counts the value  which is then linked to the calenader against the "ApplicationDate" which is how I get the breakdown by Month and Quarter for each data type.  I am maybe needing to cover all date ranges in the calendar and any that are missing from my data soure are populated with a '0' in the "DataValue" column for each table.

Sample Data Type2

ApplicationDate	DataType	DataValue
02/05/2019	Data2	1
29/10/2018	Data2	1
27/09/2019	Data2	1
27/09/2019	Data2	1
03/10/2019	Data2	1
18/11/2019	Data2	1
18/11/2019	Data2	1
08/11/2019	Data2	1
22/10/2019	Data2	1
28/02/2020	Data2	1
28/02/2020	Data2	1
09/04/2020	Data2	1

Or am I over complicating this task?

many thanks

Chris

[amitchandak]

@Anonymous , +0 to you measure

or try this option

[Anonymous]

Amitchandak

Thats for this, definatley worth a look at but for now I think gregs answer seems to work a treat plus I like the idea of using DAX.

thanks again.

Chris

[Greg_Deckler · Accepted solution]

@Anonymous - Often you can get around this by doing something like:

Measure = 

  VAR __Calc = <some calculation>

RETURN

  IF(ISBLANK(__Calc),0,__Calc)

[Anonymous]

Greg

Thats is so simple, is the measure working through each row; row context, to carry-out the 'IF' statement?

see chart with forumla:

My actual formula is  - 

=VAR _CALC = COUNTA(Data2_nonWT[FieldName])
RETURN
IF(ISBLANK(_CALC), 0, _CALC)

I take it the fact I have the data2 table linked to the Calenader table (1-* relationship) must also playa key role? 

One other query, the red dots on the chart.  I can not seem to sort the Months right for 'Q2', the rest seem to fall into place, any thoughts on this?

Chris