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
Anonymous
Not applicable

Line Chart showing Prior dates

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

 

Capture.PNG

 

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

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

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast


View solution in original post

8 REPLIES 8

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 

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast


Anonymous
Not applicable

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!

Hi @Anonymous ,

 

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

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast


Anonymous
Not applicable

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

 

Capture1.png

 

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.

 

Capture2.PNG

 

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

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]?

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast


Anonymous
Not applicable

Hi @mwegener ,

 

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

@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

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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.

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast


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.