Line Chart showing Prior dates

[Anonymous]

Hi,

I'm trying to create a Line Chart to show the Average and also the UCL and LCL.

To calculate this, I have the following metric:

[No. of Incidents]

[Incident_Month_Year]

I also have some additional columns as well, but they aren't relevant.

I calculate the No. of Observed Months:

Observations Tot Incidents = CALCULATE( DISTINCTCOUNT('Table1'[Incident_Month_Year]),ALLEXCEPT('Table1',[Incident_Month_Year])) 

Which I use to to draw out the Average No. of Incidents Per Month

Avg Cnt Incidents = CALCULATE(sum('Table1'[Tot_#_Incidents])/[Observations Tot Incidents], ALLEXCEPT('Table1','Table1'[Incident_Month_Year])) 

Unfortunately, when I lay out the above onto a line chart, i see that the Avg Cnt Incidents and Observation Tot Incident Measures have extended themselves to dates prior to what I have in [Incident_Month_Year].

Is there any way to filter out my DAX correctly to only show those months which have data?

[mwegener]

Hi @Anonymous ,

at first try to change this...

Avg Cnt Incidents = CALCULATE(DIVIDE(sum('Table1'[Tot_#_Incidents]),[Observations Tot Incidents]), ALLEXCEPT('Table1','Table1'[Incident_Month_Year])) 

https://docs.microsoft.com/en-us/dax/divide-function-dax 

[Anonymous]

Hi @mwegener ,

Made the changes, but still no changes in the Line Chart as it still is showing information to Jan 2018. My data only goes back to June 2018!

[mwegener]

Hi @Anonymous ,

could you give an overview of your model and the values on your line chart?

[Anonymous]

@mwegener 

I'm trying to create a Line Chart which showcases the average of the Total No. of Incidents across the available months of data as a straight line.

I currently have a column called [Tot_#_Incidents] with Values for each Month up until June 2019. 

When I try to calculate the number of observations and average, I can see that it extrapolates to include months which I have no data. However, the number of observed month is correct as 19 is the no. of months from June 2018 - December 2019.

Is there any way I can filter out the Measure used to only include those months where No. of Incidents > 0 ?

[mwegener]

Hi @Anonymous ,

i think so.  Could you post your measure definition?

Can't you put a visual filter (line chart) on the measure [Tot_#_Incidents]?

[Anonymous]

Hi @mwegener ,

Yes - aware of the Visual Filter, but would rather make the modications on the Measures.

[v-chuncz-msft]

@Anonymous 

You may take a look at the post below and try ISEMPTY if necessary.

https://community.powerbi.com/t5/Desktop/Forecasts-for-year-and-actuals-for-ytd-only/td-p/825702

[mwegener · Accepted solution]

Hi @Anonymous 

insert your DAX calculation (for Observations Tot Incidents) in this IF statement.

[Observations Tot Incidents] =
IF ( ISBLANK ( [Tot_#_Incidents] ), BLANK (), <DAX for Observations Tot Incidents> )

If I answered your question, please mark my post as solution, this will also help others.

Please give Kudos for support.