Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
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
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
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
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
User | Count |
---|---|
141 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
123 | |
101 | |
71 | |
61 |