topic Linestx when no data is available on x axis in DAX Commands and Tips

Linestx when no data is available on x axis

Ethanhunt123 — Fri, 06 Sep 2024 15:54:55 GMT

I have created the DAX for linestx below -

Revenue trend =

VAR line =

LINESTX (

ALLSELECTED('Sales order line fact'[Order month]), -- This respects the slicer

[Total revenue trend],

'Sales order line fact'[Order month]

)

VAR slope = SELECTCOLUMNS(line, [Slope1])

VAR intercept = SELECTCOLUMNS(line, [Intercept])

VAR x = SELECTEDVALUE('Sales order line fact'[Order month])

VAR y = x * slope + intercept

RETURN y

My data has some gaps between them

May and June are not available

PBI is giving some weird results for those months. However, I need PBI to consider the total revenue trend as 0 and then calculate the revenue trend. Revenue trend for May and June should be around if total revenue is 0 for those months

57838.21

,51566.91

Re: Linestx when no data is available on x axis

lbendlin — Fri, 06 Sep 2024 18:00:07 GMT

You are trying to do math with months? IE the integer representation for the first day of the month?

Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

Re: Linestx when no data is available on x axis

Ethanhunt123 — Fri, 06 Sep 2024 18:02:20 GMT

The months are date not text. 2023 Apr is 4/1/2023

Re: Linestx when no data is available on x axis

lbendlin — Fri, 06 Sep 2024 18:56:21 GMT

That will create wonky results as the month start dates are not linear.