Scribd
Upload
106 views2 pages

DAX Calendar Functions Overview

This document defines functions to generate calendar date attributes from a date field in Power BI. It creates columns for the year, month, day, week number, semester, trimester, bimester, and other attributes. Functions like CALENDAR, YEAR, MONTH, DAY, and WEEKNUM are used to extract the attributes from the date field. Formatted text like "S1" or "T2" is concatenated with attribute values to generate labels like semester, trimester, etc.

Uploaded by

AC Alvaro
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
106 views2 pages

DAX Calendar Functions Overview

This document defines functions to generate calendar date attributes from a date field in Power BI. It creates columns for the year, month, day, week number, semester, trimester, bimester, and other attributes. Functions like CALENDAR, YEAR, MONTH, DAY, and WEEKNUM are used to extract the attributes from the date field. Formatted text like "S1" or "T2" is concatenated with attribute values to generate labels like semester, trimester, etc.

Uploaded by

AC Alvaro
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

dFechas =

var Fecha = CALENDAR(DATE(2013;1;1);DATE(2021;12;31))

return

SELECTCOLUMNS(Fecha;”Fecha”;[Date];”IdFecha”;YEAR([Date])*10000+MONTH([Date])*100 +
DAY([Date]);”IdFechaEntero”;INT([Date]);”Ejercicio” ; YEAR([Date]);”Mes”;
UPPER(FORMAT([Date];”MMMM”));”MesNro”;INT(FORMAT([Date];”M”));”NroDia”;INT(FORM
AT([Date];”d”));”Trimestre”;”T”&ROUNDUP(MONTH([Date])/3;0);”NroTrimestre”;ROUNDUP(
MONTH([Date])/3;0);”DiaSemana”;WEEKDAY([Date];2);”Semana”;WEEKNUM([Date];2);”Nomb
re Dia”;UPPER(FORMAT([Date];”DDDD”));”EjercicioTrimestre”;COMBINEVALUES(“-
“;YEAR([Date]);”T”&ROUNDUP(MONTH([Date])/3;0));”Nº Semana ISO”;ROUNDDOWN(([Date]-
DATE(year([Date]-WEEKDAY([Date]-1)+4);1;3)+WEEKDAY(DATE(YEAR([Date]-WEEKDAY([Date]-
1)+4);1;3))+5)/7;0);”MesCorto”;UPPER(FORMAT([Date];”MMM”));”DiaEjercicio”;UPPER(FORM
AT([Date];”Y”));”EjercicioActual”;YEAR(TODAY());”Mes
Actual”;MONTH(TODAY());”HoyEntero”;INT(today());”Trimestre
Actual”;ROUNDUP(MONTH(TODAY())/3;0);”Semestre”;ROUNDUP(INT(FORMAT([Date];”M”))*2
/12;0);”EjercicioMes”;COMBINEVALUES(“-
“;Year([Date]);FORMAT([Date];”MM”));”EjercicioISO” ; IF(OR((ROUNDDOWN(([Date]-
DATE(year([Date]-WEEKDAY([Date]-1)+4);1;3)+WEEKDAY(DATE(YEAR([Date]-WEEKDAY([Date]-
1)+4);1;3))+5)/7;0))=53; (ROUNDDOWN(([Date]-DATE(year([Date]-WEEKDAY([Date]-
1)+4);1;3)+WEEKDAY(DATE(YEAR([Date]-WEEKDAY([Date]-
1)+4);1;3))+5)/7;0))=52)&&MONTH([Date])=1;YEAR([Date])-1;IF((ROUNDDOWN(([Date]-
DATE(year([Date]-WEEKDAY([Date]-1)+4);1;3)+WEEKDAY(DATE(YEAR([Date]-WEEKDAY([Date]-
1)+4);1;3))+5)/7;0))=1&&MONTH([Date])=12;YEAR([Date])+1;YEAR([Date])));”Desvio Ejercicio”
; YEAR([Date])-YEAR(TODAY());”Desvio Mes” ; IF((YEAR([Date])-
YEAR(TODAY()))=0;INT(FORMAT([Date];”M”))-
INT(FORMAT(TODAY();”M”));INT(FORMAT([Date];”M”))-
INT(FORMAT(TODAY();”M”))+(12*(YEAR([Date])-YEAR(TODAY()))));”Desvio Dia” ; INT([Date])-
INT(Today());”HastaFecha” ; IF((INT([Date])-INT(Today()))<=0;”PASADO”;”FUTURO”);”Desvio
Trimestre” ; IF((YEAR([Date])-YEAR(TODAY()))=0;(ROUNDUP(MONTH([Date])/3;0))-
(ROUNDUP(MONTH(TODAY())/3;0));(ROUNDUP(MONTH([Date])/3;0))-
(ROUNDUP(MONTH(TODAY())/3;0))+(4*(YEAR([Date])-YEAR(TODAY()))));”Semestre Actual” ;
ROUNDUP(MONTH(TODAY())*2/12;0);”Desvio Semestre” ; IF(YEAR([Date])-
YEAR(TODAY())=0;(ROUNDUP(INT(FORMAT([Date];”M”))*2/12;0))-
(ROUNDUP(MONTH(TODAY())*2/12;0));(ROUNDUP(INT(FORMAT([Date];”M”))*2/12;0))-
(ROUNDUP(MONTH(TODAY())*2/12;0))+(2*(YEAR([Date])-YEAR(TODAY())))))
Calendario=Calendar(MIN(Ventas[Fecha]);Today())

Año=YEAR(Calendario[Fecha])

Mes=MONTH(Calendario[Fecha])

Dia=DAY(Calendario[Fecha])

FechaSk = Calendario[Año]*10000 + Calendario[Mes]*100 + Calendario[Dia]

Semestre=ROUNDUP(Calendario[Mes]/6;0)

Cuatrimestre=ROUNDUP(Calendario[Mes]/4;0)

Trimestre=ROUNDUP(Calendario[Mes]/3;0)

Bimestre=ROUNDUP(Calendario[Mes]/2;0)

Semana = WEEKNUM(Calendario[Fecha])

AñoMes = Calendario[Año] & FORMAT(Calendario[Fecha];"mm")

DiaSemana=WEEKDAY(Calendario[Fecha])

NbAño = Calendario[Año]

NbSemestre = "S"&Calendario[Semestre]

NbSemestreAño = Calendario[NbSemestre] & "/" & RIGHT(Calendario[NbAño];2)

NbCuatrimestre = "C"&Calendario[Cuatrimestre]

NbCuatrimestreAño = Calendario[NbCuatrimestre] &"/" & RIGHT(Calendario[Año];2)

NbTrimestre = "T"&Calendario[Trimestre]

NbTrimestreAño = Calendario[NbTrimestre] & "/" & RIGHT(Calendario[Año];2)

NbBimestre = "B" & Calendario[Bimestre]

NbBimestreAño = Calendario[NbBimestre] &"/" & RIGHT(Calendario[Año];2)

NbMes = Calendario[Fecha].[Mes]

nbMes3L = left(Calendario[NbMes];3)

NbDia = Calendario[Dia] & " " & Calendario[nbMes3L]

NbSemana = "Sem " & Calendario[Semana] & "/" & RIGHT(Calendario[Año];2)

NdDiaSemana = FORMAT(Calendario[Fecha];"dddd")

Power BI Gráficos de Alto Impacto con DAX - Seminario Completo

You might also like