Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jorgus
Frequent Visitor

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

2020-10-05_09-22-15.png

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

1 ACCEPTED SOLUTION

@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 

SU18_powerbi_badge

View solution in original post

3 REPLIES 3
AlB
Super User
Super User

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 

SU18_powerbi_badge

jorgus
Frequent Visitor

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

Relationship-Calendar-OraVentas.png

 

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

@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 

SU18_powerbi_badge

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.