Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Stop showing useless dates - Date intelligence

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

Need Help
  • v-xicai's avatar
    v-xicai
    7 years ago

    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.

1 Reply

  • v-xicai's avatar
    v-xicai
    Community Support
    7 years ago

    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.