Forum Discussion

jorgus's avatar
jorgus
Frequent Visitor
5 years ago
Solved

Count Total Distinct Dates for a Month in a Production Table

Hi there.   I have a production table with dates and other values, this table si related to a calendar table. I need to count the total of distincts date in a month, to do this I created this meas...
Need Help
  • AlB's avatar
    AlB
    5 years ago

    jorgus 

    Ok, you are using Fecha in the visual instead of Date so we need to do the ALL( ) on Fecha:

    CaCantidadias Tabla VeVentas 
CALCULATE(
    DIDISTINCTCOUNTroraentas[CAB_FECHA]),
    ALL(Calendario[FeFecha
)

    It is probably best to only have one column with the date in the calendar table rather that, to avoid potential confusion like here.

    Please mark the question solved when done and consider giving kudos if posts are helpful.

    Contact me privately for support with any larger-scale BI needs, tutoring, etc.

    Cheers 

AlB's avatar
AlB
Icon for Community Champion rankCommunity Champion
5 years ago

Hi jorgus 

Assuming the fields you use in the visuals are from the calendar table, try:

Cantidad Dias Tabla Ventas = 
CALCULATE(
    DISTINCTCOUNT(ora_ventas[CAB_FECHA]),
    ALL(CalendarT[Date])
)

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

jorgus's avatar
jorgus
Frequent Visitor
5 years ago

hi AlB thanks, for you reply

I change the measure, but the problem persist

 

Cantidad Dias Tabla Ventas = 
CALCULATE(
    DISTINCTCOUNT(ora_ventas[CAB_FECHA]),
    ALL(Calendario[Date])
)

 

My Calendar Table:

 

Calendario = 
VAR Days = CALENDARAUTO() 
RETURN ADDCOLUMNS (
    Days,
	"Fecha", FORMAT([Date], "DD/MM/YYYY"),
    "Año", YEAR ([Date]),
	"Año Mes Codigo",  (YEAR([Date]) * 100) + MONTH ([Date]),
	"Año Mes Corto", FORMAT ([Date], "YYYY-MMM"),
    "Año Mes", FORMAT ([Date], "YYYY-MMMM"),
    "Trimestre Nro", TRUNC ( ( MONTH ( [Date] ) - 1 ) / 3 ) + 1,
    "Trimestre", "T" & TRUNC ( ( MONTH ( [Date] ) - 1 ) / 3 ) + 1,
    "Trimestre Quarter Nro", FORMAT([Date], "Q"),
    "Trimestre Quarter", "T" & FORMAT([Date], "Q"),
    "Trimestre Quarter Año", "T" & FORMAT([Date], "Q-YYYY"),
    "Mes", MONTH ([Date]),	
    "Mes Nombre Año", FORMAT ([Date], "MMMM YYYY"),
    "Mes Nombre", FORMAT ([Date], "MMMM"),
	"Mes Corto", FORMAT ([Date], "MMM"),
	"Mes Año", FORMAT ([Date], "mmm yy"),    
	"Dia Numero", Day ([Date])    
)

 

The relationship

 

For the visual matrix it shows: Mes Corto, Fecha Corta 2, Cantidad Dias Tabla Ventas

  • AlB's avatar
    AlB
    Icon for Community Champion rankCommunity Champion
    5 years ago

    jorgus 

    Ok, you are using Fecha in the visual instead of Date so we need to do the ALL( ) on Fecha:

    CaCantidadias Tabla VeVentas 
CALCULATE(
    DIDISTINCTCOUNTroraentas[CAB_FECHA]),
    ALL(Calendario[FeFecha
)

    It is probably best to only have one column with the date in the calendar table rather that, to avoid potential confusion like here.

    Please mark the question solved when done and consider giving kudos if posts are helpful.

    Contact me privately for support with any larger-scale BI needs, tutoring, etc.

    Cheers