Forum Discussion

jorgus's avatar
jorgus
Frequent Visitor
6 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 measure

 

 

Cantidad Dias Tabla Ventas = 
CALCULATE(
    DISTINCTCOUNT(ora_ventas[CAB_FECHA])
)

 

 

It works fine with months, but when I add date I get 1 instead of the total of the selected month

And I need to always get the total for the month for every showed date

Need Help
  • AlB's avatar
    AlB
    6 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 

3 Replies

  • AlB's avatar
    AlB
    Icon for Community Champion rankCommunity Champion
    6 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
      6 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
        6 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