Forum Discussion
AdministratorCategorical Line Chart with Continuous Lines
Hello everyone!
I would like to get a line graph with all the day of the month numbers, right now I have the continuous rate applied, but I don't get them every day of the month.
But if I put categorical all the numbers apply to me, the only thing is that if there are values at 0 or null, I am left with a space and it looks bad.
And this is the code in case I need to change something in it so that the goal is to visualize each and every day of the month but that if there are 0 or null values, the line is continuous.
Encargos_Dinamico_Email =
VAR Hoy = TODAY()
VAR DiaActual = DAY(Hoy)
VAR MesActual = MONTH(Hoy)
VAR AnioActual = YEAR(Hoy)
-- Mes y año anterior
VAR MesAnterior = IF(MesActual = 1, 12, MesActual - 1)
VAR AnioMesAnterior = IF(MesActual = 1, AnioActual - 1, AnioActual)
-- Fecha límite siempre es hasta ayer
VAR FechaLimiteActual = Hoy - 1
-- Fechas de inicio y fin para año actual
VAR FechaInicioActual =
IF(
DiaActual = 1,
DATE(AnioMesAnterior, MesAnterior, 1), -- Si es día 1, mes anterior completo
DATE(AnioActual, MesActual, 1) -- Si no, desde el inicio del mes actual
)
VAR FechaFinActual =
IF(
DiaActual = 1,
EOMONTH(DATE(AnioMesAnterior, MesAnterior, 1), 0), -- Hasta fin del mes anterior
FechaLimiteActual -- Hasta ayer
)
-- Fechas equivalentes para el año pasado
VAR FechaInicioAnterior = DATE(YEAR(FechaInicioActual) - 1, MONTH(FechaInicioActual), 1)
VAR FechaFinAnterior =
IF(
DiaActual = 1,
EOMONTH(FechaInicioAnterior, 0), -- Mes anterior del año pasado completo
DATE(YEAR(FechaLimiteActual) - 1, MONTH(FechaLimiteActual), DAY(FechaLimiteActual))
)
RETURN
CALCULATE(
DISTINCTCOUNT(EncargosAnx[encargos_id]),
FILTER(
EncargosAnx,
(
EncargosAnx[fecha] >= FechaInicioActual &&
EncargosAnx[fecha] <= FechaFinActual
)
||
(
EncargosAnx[fecha] >= FechaInicioAnterior &&
EncargosAnx[fecha] <= FechaFinAnterior
)
)
)I appreciate any help for this case.
Thanks a lot
Could you please follow these steps below:
1) Load data
2) Create the relationship
- Model view >> drag Calendar[Date] >>Fact[Date] (Many-to-one, single direction from Calendar >> Fact).
- In Table tools, Mark as date table = Calendar, with Date.
3) Create measures (ignore 0s and connect across blanks)
In Fact table (or a Measures table), add:
Total Value = SUM ( Fact[Value] )Value (ignore zero) = VAR v = [Total Value] RETURN IF ( ISBLANK(v) || v = 0, BLANK(), v )Value (connect across blanks) = VAR currDate = MAX ( Calendar[Date] ) VAR lastGoodDate = CALCULATE ( LASTNONBLANK ( Calendar[Date], [Value (ignore zero)] ), FILTER ( ALL ( Calendar[Date] ), Calendar[Date] <= currDate ) ) RETURN CALCULATE ( [Value (ignore zero)], Calendar[Date] = lastGoodDate )What this does:
- Days with 0 >> treated as blank.
- On blank days, it shows the last known value (so the line doesn’t break).
- If no previous value exists yet, it stays blank until the first data point.
4) Build the visual
- Insert Line chart.
- X-axis: Calendar[Date]
- In Format >> X-axis >> Type: Continuous
- Y-axis: Value (connect across blanks)
- Add a Month slicer (e.g., Calendar[Month] or use a filter pane) so the axis is exactly that month.
6 Replies
rohit1991Super User
Could you please follow these steps below:
1) Load data
2) Create the relationship
- Model view >> drag Calendar[Date] >>Fact[Date] (Many-to-one, single direction from Calendar >> Fact).
- In Table tools, Mark as date table = Calendar, with Date.
3) Create measures (ignore 0s and connect across blanks)
In Fact table (or a Measures table), add:
Total Value = SUM ( Fact[Value] )Value (ignore zero) = VAR v = [Total Value] RETURN IF ( ISBLANK(v) || v = 0, BLANK(), v )Value (connect across blanks) = VAR currDate = MAX ( Calendar[Date] ) VAR lastGoodDate = CALCULATE ( LASTNONBLANK ( Calendar[Date], [Value (ignore zero)] ), FILTER ( ALL ( Calendar[Date] ), Calendar[Date] <= currDate ) ) RETURN CALCULATE ( [Value (ignore zero)], Calendar[Date] = lastGoodDate )What this does:
- Days with 0 >> treated as blank.
- On blank days, it shows the last known value (so the line doesn’t break).
- If no previous value exists yet, it stays blank until the first data point.
4) Build the visual
- Insert Line chart.
- X-axis: Calendar[Date]
- In Format >> X-axis >> Type: Continuous
- Y-axis: Value (connect across blanks)
- Add a Month slicer (e.g., Calendar[Month] or use a filter pane) so the axis is exactly that month.
- Rupak_bi
Hi Syndicate_Admin ,
The quick fix will be calculating the average (mid value) on those blank dates based on previous and next value.
if there is two or three consecutive gaps, the DAX will be a bit complex but the same mathemetical approach can be taken. Share some sample data......will try....
Syndicate_AdminHello, thank you very much for the message.
This solution does not show me EVERY day on the X axis, the focus if I put continuous, I get the straight line but I don't get it EVERY day, on the other hand if I put categorical, I get every day on the X axis, but I get the spaces.
I don't know if this explains the problem a little better.Thanks a lot
Hi Syndicate_Admin ,
Did you get a chance to check rohit1991's solution? It uses a proper Calendar table with measures that ignore blanks/zeros and carry forward the last value, ensuring all days show on a continuous X-axis with no line breaks.
Hi Syndicate_Admin ,
I would also take a moment to thank rohit1991 , for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions
Regards,
Sreeteja.Hi Syndicate_Admin ,
I hope the information provided above assists you in resolving the issue. If you have any additional questions or concerns, please do not hesitate to contact us.
