X-Axis Constant Line Missing Even with Date Data Type

[JST15]

Hi all! 

I've seen a lot posted about x-axis constant but I still can't figure out why mine isn't showing. Please see below. I'm using a line chart with Date hierarchy (year and month) but don't see the X-Axis Constant Line option. Does anyone know why? Any help on why is greatly appreciated. 

Side question - has anyone created a verticle line that can auto shift based on the current month? I'm creating a actuals to forecast report where the reports refreshes each month once actuals are posted. The verticle line would simple distinguish the actuals from forecast. The line is a client request as they don't like the solid line for actuals vs dotted line for forecast. 

[NaveenGandhi · Accepted solution]

Hi

The x-axis needs to be numeric[ex: year], so that you can quantify where the constant line in x axis should be added. Since you have month/year[which is string+numeric] on your x axis, you lose the ability to add x axis constant line.

With current structure you can leverage error bars to add a line as below.

Error =

Var tes = CALCULATE(max(FactSales[SalesAmount]),ALLEXCEPT(FactSales,DimDate[CalendarYearLabel],DimDate[FiscalMonthLabel]))

return if(ENDOFMONTH(DimDate[Datekey])=DATE(2008,08,31),tes,0)


Replace the DATE(2008,08,31) to capture you actuals end month, I would advice to make it dynamic to change each month. Then add erro bar as below, Format the bar as per your need.



You can play around with both upper and lower bound[Best practice to make them dynamic according to your y axis values] to create even longer lines to mimic constant lines.

Will this be a good solution for you?

 If this post helps, then please consider Accept it as the solution to help the others find it more quickly. Appreciate you kudos!!

[NaveenGandhi · Accepted solution]

Hi

The x-axis needs to be numeric[ex: year], so that you can quantify where the constant line in x axis should be added. Since you have month/year[which is string+numeric] on your x axis, you lose the ability to add x axis constant line.

With current structure you can leverage error bars to add a line as below.

Error =

Var tes = CALCULATE(max(FactSales[SalesAmount]),ALLEXCEPT(FactSales,DimDate[CalendarYearLabel],DimDate[FiscalMonthLabel]))

return if(ENDOFMONTH(DimDate[Datekey])=DATE(2008,08,31),tes,0)


Replace the DATE(2008,08,31) to capture you actuals end month, I would advice to make it dynamic to change each month. Then add erro bar as below, Format the bar as per your need.



You can play around with both upper and lower bound[Best practice to make them dynamic according to your y axis values] to create even longer lines to mimic constant lines.

Will this be a good solution for you?

 If this post helps, then please consider Accept it as the solution to help the others find it more quickly. Appreciate you kudos!!

[ParsaOmidvar]

Thank You It Really Helped me

[JST15 · Accepted solution]

This solution is working great. Thank you!

[NaveenGandhi]

Glad to hear @JST15 

Can you mark my response as solution?

Thanks,

Naveen