@Melissa
Hello Melissa! Is there any way to modify this so that the Fiscal Week starts in the stated date regardless of the weekday, and contains 7 days in the first week of the FY?
Hello Forum,
I am new. How I can post a topic on the Power Query Category?
Thanks 
Hi @Melissa !
Thanks for a great date table. Are there any plans for adding Tertial columns into the base version or should I make a try on my own 
?
Thanks
Hi @CATZ.
Firstly, please do not post to solved threads. Rather, start a new thread and link to a solved thread if it pertains to your issue. This will ensure visibility for your issue and is also for your benefit, as many forum members do not view solved threads.
@EnterpriseDNA, please split this thread into its own thread starting with the post above this one.
Secondly, an explanation and example showing your issue would help the forum members pursue your issue. I’m not sure to what you’re referring by “tertial” columns; I, for one, have not encountered the word “tertial” in 5 years of using Power BI.
Greg
1 Like
Hi !
OK, will do.
By the way, Tertial = 4 months
// CATZ
Hi!
Is there away to modify the code a bit so the end year is updated automatically?
I set and run the Query with start and End date. The Table generated fits my need.
However, when the end date is passed I need to adjust the end year manually.
= Query1(#date(2020, 1, 1), #date(2024, 12, 1), null, null, 1, null)
I was looking for something like…
= Query1(#date(2020, 1, 1), #date( YEAR(TODAY(), 1, 1), null, null, 1, null)
…but this is not working at all of course.
Thanks in advance
I recommend creating a new thread in future, as often people don’t look at closed threads.
Try replacing your YEAR(TODAY()) part with this:
Date.Year(DateTime.LocalNow())
Giving you the full query of:
Query1(#date(2020, 1, 1), #date(Date.Year(DateTime.LocalNow()) , 12, 1), null, null, 1, null)
YEAR() is a dax function, not m query for reference.
You can read more about date functions in m query here
1 Like
Thanks, it worked as I wanted, I will follow your recommendation in the future!
I am looking at a problem now where I need to know the number of workdays in a month and which day today is.
E.g. April 2025 has 22 work days in it (lets not get hung up on Easter etc.) and the 15th of April 2025 is workday number 11.
Sam has done a video on how to do this is DAX, but it seems to me that it would be better inside the date table.
Is that possible?
Thank you
Hi Melissa, carlos here 
! late to the Party here…and question, would it be possible to have ISO month on this Gem Calendar on yours?
Merci Melissa
ci joint code francisé
let
fnDateTable = ( DateDebut as date, DateFin as date, optional MoisDebutAnneeFiscale as number, optional JoursFeries as list, optional NumDebutSemaine as number, optional AjouterJoursOuvresRelatifs as logical ) as table =>
let
MoisDebutAF = List.Select({1..12}, each _ = MoisDebutAnneeFiscale){0}? ?? 1,
DebutSemaine = List.Select({0..1}, each _ = NumDebutSemaine){0}? ?? 0,
DateActuelle = Date.From(DateTimeZone.FixedUtcNow()),
NbJours = Duration.Days(Duration.From(DateFin - DateDebut)) + 1,
SemaineCommence = Day.Monday,
ListeJoursFeries = JoursFeries <> null,
Source = List.Dates(DateDebut, NbJours, Duration.From(1)),
AjouterAujourdhui = if List.Contains(Source, DateActuelle) then Source else List.Combine({Source, {DateActuelle}}),
VersTable = Table.FromColumns({AjouterAujourdhui}, type table [Date = date]),
// === Colonnes Annee ===
AjouterAnnee = Table.AddColumn(VersTable, "EnregistrementAnnee", each [
Annee = Date.Year([Date]),
DecalageAnnee = Date.Year([Date]) - Date.Year(DateActuelle),
AnneeTerminee = Date.EndOfYear([Date]) < Date.EndOfYear(DateActuelle)
], type [Annee=Int64.Type, DecalageAnnee=Int64.Type, AnneeTerminee=logical]),
EtendreAnnee = Table.ExpandRecordColumn(AjouterAnnee, "EnregistrementAnnee", {"Annee", "DecalageAnnee", "AnneeTerminee"}),
// === Colonnes Trimestre ===
AjouterTrimestre = Table.AddColumn(EtendreAnnee, "EnregistrementTrimestre", each [
NumeroTrimestre = Date.QuarterOfYear([Date]),
Trimestre = "T" & Number.ToText(#"NumeroTrimestre"),
DebutTrimestre = Date.StartOfQuarter([Date]),
FinTrimestre = Date.EndOfQuarter([Date]),
TrimestreAnnee = "T" & Number.ToText(#"NumeroTrimestre") & Date.ToText([Date], [Format = " yyyy"]),
TrimestreNAnnee = [Annee] * 10 + #"NumeroTrimestre",
DecalageTrimestre = ((4 * Date.Year([Date])) + #"NumeroTrimestre") - ((4 * Date.Year(DateActuelle)) + Date.QuarterOfYear(DateActuelle)),
TrimestreTermine = #"FinTrimestre" < Date.EndOfQuarter(DateActuelle)
], type [NumeroTrimestre=Int64.Type, Trimestre=text, DebutTrimestre=date, FinTrimestre=date, TrimestreAnnee=text, TrimestreNAnnee=Int64.Type, DecalageTrimestre=Int64.Type, TrimestreTermine=logical]),
EtendreTrimestre = Table.ExpandRecordColumn(AjouterTrimestre, "EnregistrementTrimestre", {"NumeroTrimestre", "Trimestre", "DebutTrimestre", "FinTrimestre", "TrimestreAnnee", "TrimestreNAnnee", "DecalageTrimestre", "TrimestreTermine"}),
// === Colonnes Mois ===
AjouterMois = Table.AddColumn(EtendreTrimestre, "EnregistrementMois", each [
Mois = Date.Month([Date]),
DebutMois = Date.StartOfMonth([Date]),
FinMois = Date.EndOfMonth([Date]),
MoisAnnee = Text.Proper(Date.ToText([Date], [Format = "MMM yyyy"])),
MoisNAnnee = [Annee] * 100 + Mois,
DecalageMois = ((12 * Date.Year([Date])) + Mois) - ((12 * Date.Year(DateActuelle)) + Date.Month(DateActuelle)),
MoisTermine = #"FinMois" < Date.EndOfMonth(DateActuelle),
NomMois = Text.Proper(Date.ToText([Date], "MMMM")),
MoisCourt = Text.Proper(Date.ToText([Date], "MMM")),
InitialMois = Text.Start(Text.Proper(Date.ToText([Date], "MMMM")), 1) & Text.Repeat(Character.FromNumber(8203), Mois),
JourMois = Date.Day([Date])
], type [Mois=Int64.Type, DebutMois=date, FinMois=date, MoisAnnee=text, MoisNAnnee=Int64.Type, DecalageMois=Int64.Type, MoisTermine=logical, NomMois=text, MoisCourt=text, InitialMois=text, JourMois=Int64.Type]),
EtendreMois = Table.ExpandRecordColumn(AjouterMois, "EnregistrementMois", {"Mois", "DebutMois", "FinMois", "MoisAnnee", "MoisNAnnee", "DecalageMois", "MoisTermine", "NomMois", "MoisCourt", "InitialMois", "JourMois"}),
// === Colonnes Semaine ===
AjouterSemaine = Table.AddColumn(EtendreMois, "EnregistrementSemaine", each [
NumeroSemaineCalc = Number.RoundDown((Date.DayOfYear([Date]) - (Date.DayOfWeek([Date], SemaineCommence) + 1) + 10) / 7),
NumeroSemaine = if NumeroSemaineCalc = 0 then Number.RoundDown((Date.DayOfYear(#date(Date.Year([Date]) - 1, 12, 31)) - (Date.DayOfWeek(#date(Date.Year([Date]) - 1, 12, 31), SemaineCommence) + 1) + 10) / 7) else if (NumeroSemaineCalc = 53 and (Date.DayOfWeek(#date(Date.Year([Date]), 12, 31), SemaineCommence) + 1 < 4)) then 1 else NumeroSemaineCalc,
DebutSemaineDate = Date.StartOfWeek([Date], SemaineCommence),
FinSemaineDate = Date.EndOfWeek([Date], SemaineCommence),
SemaineAnnee = "S" & Text.PadStart(Text.From(#"NumeroSemaine"), 2, "0") & " " & Text.From(Date.Year(Date.AddDays(#"DebutSemaineDate", 3))),
SemaineNAnnee = Date.Year(Date.AddDays(#"DebutSemaineDate", 3)) * 100 + #"NumeroSemaine",
DecalageSemaine = (Number.From(#"DebutSemaineDate") - Number.From(Date.StartOfWeek(DateActuelle, SemaineCommence))) / 7,
SemaineTerminee = #"FinSemaineDate" < Date.EndOfWeek(DateActuelle, SemaineCommence)
], type [NumeroSemaine=Int64.Type, DebutSemaineDate=date, FinSemaineDate=date, SemaineAnnee=text, SemaineNAnnee=Int64.Type, DecalageSemaine=Int64.Type, SemaineTerminee=logical]),
EtendreSemaine = Table.ExpandRecordColumn(AjouterSemaine, "EnregistrementSemaine", {"NumeroSemaine", "DebutSemaineDate", "FinSemaineDate", "SemaineAnnee", "SemaineNAnnee", "DecalageSemaine", "SemaineTerminee"}),
// === Colonnes Jour ===
AjouterJour = Table.AddColumn(EtendreSemaine, "EnregistrementJour", each [
NumeroJourSemaine = Date.DayOfWeek([Date], SemaineCommence) + DebutSemaine,
NomJourSemaine = Text.Proper(Date.ToText([Date], "dddd")),
InitialJourSemaine = Text.Proper(Text.Start(#"NomJourSemaine", 1)) & Text.Repeat(Character.FromNumber(8203), Date.DayOfWeek([Date], SemaineCommence) + DebutSemaine),
JourAnnee = Date.DayOfYear([Date]),
DateInt = [Annee] * 10000 + [Mois] * 100 + [JourMois],
DecalageJour = Number.From([Date]) - Number.From(DateActuelle),
ApresAujourdhui = not ([Date] <= DateActuelle),
EstJourSemaine = if Date.DayOfWeek([Date], SemaineCommence) > 4 then false else true,
EstJourFerie = if not ListeJoursFeries then "Inconnu" else List.Contains(JoursFeries, [Date]),
EstJourOuvre = if (if Date.DayOfWeek([Date], SemaineCommence) > 4 then false else true) and (if ListeJoursFeries then not List.Contains(JoursFeries, [Date]) else true) then true else false,
TypeJour = if ListeJoursFeries and List.Contains(JoursFeries, [Date]) then "JourFerie" else if (if Date.DayOfWeek([Date], SemaineCommence) > 4 then false else true) = false then "Weekend" else if (if Date.DayOfWeek([Date], SemaineCommence) > 4 then false else true) = true then "JourSemaine" else null
], type [NumeroJourSemaine=Int64.Type, NomJourSemaine=text, InitialJourSemaine=text, JourAnnee=Int64.Type, DateInt=Int64.Type, DecalageJour=Int64.Type, ApresAujourdhui=logical, EstJourSemaine=logical, EstJourFerie=(if ListeJoursFeries then Logical.Type else Text.Type), EstJourOuvre=logical, TypeJour=text]),
EtendreJour = Table.ExpandRecordColumn(AjouterJour, "EnregistrementJour", {"NumeroJourSemaine", "NomJourSemaine", "InitialJourSemaine", "JourAnnee", "DateInt", "DecalageJour", "ApresAujourdhui", "EstJourSemaine", "EstJourFerie", "EstJourOuvre", "TypeJour"}),
// === Colonnes ISO ===
AjouterISOAnnee = Table.AddColumn(EtendreJour, "ISOAnnee", each Date.Year(Date.AddDays(Date.StartOfWeek([Date], SemaineCommence), 3)), Int64.Type),
AjouterISOTrimestreNum = Table.AddColumn(AjouterISOAnnee, "ISONumeroTrimestre", each if [NumeroSemaine] > 39 then 4 else if [NumeroSemaine] > 26 then 3 else if [NumeroSemaine] > 13 then 2 else 1, Int64.Type),
AjouterISOTrimestre = Table.AddColumn(AjouterISOTrimestreNum, "ISOTrimestre", each "T" & Number.ToText([ISONumeroTrimestre]), type text),
AjouterISOTrimestreAnnee = Table.AddColumn(AjouterISOTrimestre, "ISOTrimestreAnnee", each "T" & Number.ToText([ISONumeroTrimestre]) & " " & Number.ToText([ISOAnnee]), type text),
AjouterISOTrimestreNAnnee = Table.AddColumn(AjouterISOTrimestreAnnee, "ISOTrimestreNAnnee", each [ISOAnnee] * 10 + [ISONumeroTrimestre], Int64.Type),
// === Colonnes Fiscales ===
AjouterNumeroAF = Table.AddColumn(AjouterISOTrimestreNAnnee, "NumeroAF", each (if [Mois] >= MoisDebutAF and MoisDebutAF >1 then [Annee] +1 else [Annee]), Int64.Type),
AjouterAnneeFiscale = Table.AddColumn(AjouterNumeroAF, "AnneeFiscale", each "AF" & Text.From([NumeroAF]), type text),
AjouterTrimestreFiscal = Table.AddColumn(AjouterAnneeFiscale, "TrimestreFiscal", each "TF" & Text.From(Number.RoundUp(Date.Month(Date.AddMonths([Date], - (MoisDebutAF -1))) / 3)) & " " & Text.From([NumeroAF]), type text),
AjouterTrimestreNAnneeFiscal = Table.AddColumn(AjouterTrimestreFiscal, "TrimestreNAnneeFiscal", each [NumeroAF] * 10 + Number.RoundUp(Date.Month(Date.AddMonths([Date], - (MoisDebutAF -1))) / 3), type number),
AjouterMoisFiscalNum = Table.AddColumn(AjouterTrimestreNAnneeFiscal, "NumeroMoisFiscal", each if [Mois] >= MoisDebutAF and MoisDebutAF >1 then [Mois] - (MoisDebutAF-1) else if [Mois] >= MoisDebutAF and MoisDebutAF =1 then [Mois] else [Mois] + (12-MoisDebutAF+1), type number),
AjouterMoisFiscal = Table.AddColumn(AjouterMoisFiscalNum, "MoisFiscal", each "MF" & Text.PadStart(Text.From([NumeroMoisFiscal]), 2, "0") & " " & Text.From([NumeroAF]), type text),
AjouterMoisNAnneeFiscal = Table.AddColumn(AjouterMoisFiscal, "MoisNAnneeFiscal", each [NumeroAF] * 100 + [NumeroMoisFiscal], type number),
AjouterPremierJourAF = Table.AddColumn(AjouterMoisNAnneeFiscal, "PremierJourAF", each if [Mois] >= MoisDebutAF then #date([Annee], MoisDebutAF, 1) else #date([Annee]-1, MoisDebutAF, 1), type date),
AjouterSemaineFiscalNum = Table.AddColumn(AjouterPremierJourAF, "NumeroSemaineFiscale", each let
DebutSemaineAF = Date.StartOfWeek([PremierJourAF], SemaineCommence),
DebutSemaineCourante = Date.StartOfWeek([Date], SemaineCommence),
DiffSemaines = Duration.Days(DebutSemaineCourante - DebutSemaineAF) / 7
in Number.RoundDown(DiffSemaines) + 1, Int64.Type
),
AjouterSemaineFiscale = Table.AddColumn(AjouterSemaineFiscalNum, "SemaineFiscale", each "SF" & Text.PadStart(Text.From([NumeroSemaineFiscale]), 2, "0") & " " & Text.From([NumeroAF]), type text),
AjouterSemaineNAnneeFiscal = Table.AddColumn(AjouterSemaineFiscale, "SemaineNAnneeFiscal", each [NumeroAF] * 100 + [NumeroSemaineFiscale], Int64.Type)
in
AjouterSemaineNAnneeFiscal
in
Value.ReplaceType(fnDateTable, Value.ReplaceMetadata(Value.Type(fnDateTable), [
Documentation.Name = " fxCalendrier",
Documentation.Description = " Fonction pour créer une table de dates ISO-8601",
Documentation.LongDescription = " Fonction pour créer une table de dates ISO-8601 avec colonnes francisées (sans accents, sans espaces)",
Documentation.Category = " Table",
Documentation.Version = " 2.04: Version Optimisée",
Documentation.Source = " local",
Documentation.Author = " Adaptation FR",
Documentation.Examples = { [Description = " Exemple d’utilisation avec paramètres FR", Code = " fnDateTable(#date(2020,1,1), #date(2030,12,31), 1, null, 1, true) ", Result = " "] }
]))
Hi Carlos,
To the best of my knowledge the ISO-8601 week calendar doesn’t define months. A common requirement is to treat each quarter as 13 weeks, using a retail calendar pattern such as 4-4-5, 4-5-4, or 5-4-4.
Below is a custom function fxISOMonths that maps an ISO week number (1 - 53) to a corresponding month index according to the selected 13-week pattern. When a year includes a 53rd week, you can optionally collapse the resulting partial 13th month into month 12.
Create a custom function - named: fxISOMonths
(isoWeek as number, pattern as text, optional collapseToM12 as logical) as number =>
let
p = Text.Select(pattern, Text.ToList("45")),
errWK = if (isoWeek < 1 or isoWeek > 53)
then error "fxISOMonths: isoWeek must be an integer between 1 and 53 (inclusive)."
else null,
errPAT = if not (Text.Length(p) =3 and Text.Length(Text.Select(p, "5")) =1)
then error "fxISOMonths: pattern must be three numbers (4, 5) that sum to 13 (e.g. 4-4-5, 4-5-4, 5-4-4)."
else null,
collapse = (isoWeek = 53) and (collapseToM12 ?? false),
q = Number.RoundUp(isoWeek /13),
wq = let x = isoWeek - (q - 1) * 13 in if x > 13 then 13 else x,
c1 = if Text.StartsWith(p, "5") then 5 else 4,
c2 = if Text.EndsWith(p, "5") then 8 else 9,
iq = if wq <= c1 then 1 else if wq <= c2 then 2 else 3,
isoMonth = (q - 1) * 3 + iq,
r = if (errWK & errPAT) = null
then if collapse then 12 else isoMonth
else null
in
r
Invoke the fxCalendar. Here’s how to invoke the fxISOMonths function; pass Week Number a pattern and optionally collapseToM12 (true, collapses a 13th month into month 12). A demonstration below, notice that I’ve kept a selection of colums, RemoveOthers, you can remove that step once you are happy with the result…
let
Source = fxCalendar(#date(2016, 1, 1), #date(2026, 12, 31), 7, null, 1, null),
ISOMonth445 = Table.AddColumn(Source, "ISO Month 445", each fxISOMonths([Week Number], "4-4-5"), type number),
ISOMonth454 = Table.AddColumn(ISOMonth445, "ISO Month 454", each fxISOMonths([Week Number], "4-5-4"), type number),
ISOMonth544 = Table.AddColumn(ISOMonth454, "ISO Month 544", each fxISOMonths([Week Number], "5-4-4"), type number),
RemoveOthers = Table.SelectColumns(ISOMonth544,{"Date", "Year", "ISO Year", "ISO Quarter", "Week Number", "ISO Month 445", "ISO Month 454", "ISO Month 544"})
in
RemoveOthers
1 Like