Stop showing useless dates - Date intelligence

[Anonymous]

Hi,

I'm having trouble with a date variable. This variable is a workaround in SQL that only shows data from each monday of every week in the last 6 months:

SELECT *
FROM [MAC].[dbo].[Clientes_MAC]
where FechaCarga in (SELECT DATEADD(ww, DATEDIFF(ww,0,FechaCarga), 0))
and FechaCarga >= dateadd(month, -6, (select max(FechaCarga) from [MAC].[dbo].[Clientes_MAC]))

For some visualizations it only shows the available dates, but I'm trying to create a graph and FechaCarga has all the dates. Im more than sure that this is not the case because the query is working, so I want to know if there is a way to prevent the empty dates from showing.

Thanks in advance,

IC

[v-xicai · Accepted solution]

Hi @Anonymous ,

You can create calendar table to get all date like DAX below, then create relationship between this calendar table and your original table on date field, keep Cross filter direction as Both.

DateDim= CALENDARAUTO()

DateDim = CALENDAR(MIN(Data[DATE]),MAX(Data[DATE]))

Best Regards,

Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.