Power BI's own date table (auto date/time) has Gregorian years, quarters, months and days, and nothing about the weekend, Hijri months or Ramadan (Microsoft Learn). A Gulf business runs on more than that: Ramadan and Eid move every year, the Hijri month decides the season, and the weekend depends on the country and the year. Miss one of these and the report can show trends that are really the calendar.

This guide builds one calendar table in DAX that handles each of these, and three measures that use it.

Four things a standard calendar doesn't know in the Gulf

  • Ramadan moves. It started on 1 March in 2025 and on 18 February in 2026, and is expected around 8 February in 2027: about 11 days earlier each year. March 2026 against March 2025 compares two different seasons.
  • Eid is a window, not a day. The UAE's public holidays for Eid al-Fitr run from 1 to 3 Shawwal, and for Eid al-Adha from 10 to 12 Dhu al-Hijjah (u.ae). If your sales build up in the days before Eid, those days come about 11 days earlier each year and move from one Gregorian month to another.
  • The weekend changed. The UAE moved its government weekend from Friday-Saturday to Saturday-Sunday on 1 January 2022 (announced by WAM). Saudi Arabia, Kuwait, Qatar, Bahrain and Oman keep Friday-Saturday. Whichever fixed weekend a UAE calendar uses, one side of 2022 is wrong: Saturday-Sunday makes Friday 31 December 2021 a working day, Friday-Saturday makes Friday 7 January 2022 a day off.
  • Names sort alphabetically. Power BI puts text in alphabetical order unless a column is set to sort it (Microsoft Learn). So April comes before January, Wednesday before Tuesday, and the Hijri months in an order nobody uses, unless each name is sorted by its number.

Announced dates or the calculated calendar?

Power BI's date functions (YEAR, MONTH, DATE) work on the Gregorian calendar and have no Hijri month or Ramadan flag, so the calendar table has to carry the Hijri dates. The usual source is Umm al-Qura, Saudi Arabia's calculated Hijri calendar, whose tables are licensed from the Saudi government (Microsoft Learn). But Ramadan and the two Eids are set by moon sighting, and an announcement can differ from the calculation by a day.

We compared the UAE's 27 dates from 2018 to 2026 (the first day of Ramadan, Eid al-Fitr and Eid al-Adha in each year) with Umm al-Qura. 24 come from a dated announcement; three Eid al-Adha dates (2018, 2019 and 2024) are confirmed by the official celebrations held that day. 26 matched Umm al-Qura. One did not: Ramadan 2018 started on Thursday 17 May, not Wednesday 16 May. Every date and its source is in our sources file.

Dates from 2027 on are not announced yet. They are estimates: a sighting usually lands on the same day or one day apart, with no guarantee, so the calendar flags them.

Build the calendar

Open the free DAX Calendar Table Generator and set three things:

  • Hijri dates, Ramadan and Eid: on.
  • Announced Ramadan and Eid dates (UAE): on. Ramadan, Shawwal and Dhu al-Hijjah start on the UAE's announced dates, whichever weekend you pick; every other month follows Umm al-Qura.
  • Weekend: your country, or Sharjah's government. Each option knows the date its weekend changed.

Copy the DAX, then in Power BI Desktop choose Modeling → New table and paste it. Next to the usual year, quarter, month and fiscal columns, the table has these Gulf columns:

  • Hijri Year, Hijri Month Number, Hijri Day, Hijri Month Name, Hijri Date: the Hijri date of every day.
  • Is Ramadan and Ramadan Day (1 to 30), to compare Ramadan day by day.
  • Is Eid al-Fitr (1 to 3 Shawwal) and Is Eid al-Adha (10 to 13 Dhu al-Hijjah: the day of Eid and the three days of Tashreeq, which Saudi Arabia's Hajj site calls days of Eid, haj.gov.sa). They mark the days of Eid, not a country's public holidays: the UAE's Eid al-Adha holiday, for example, is 10 to 12 Dhu al-Hijjah.
  • Is Estimated Date: TRUE on Ramadan and Eid days that are not announced yet.
  • Is Weekend and Is Working Day: the weekend you picked, with its rule for each date. They don't know public holidays, half days or your employer's own rest days.
  • Is After UAE Weekend Change (UAE only): TRUE from 1 January 2022, to split working-day trends at the change.
  • Day Name (Arabic), Month Name (Arabic), Hijri Month Name (Arabic): the same names in Arabic, for Arabic report pages (the option Add Arabic name columns).

The weekend column is a plain IF on the date, so you can read it and change it. For the UAE:

Is Weekend =
IF (
    [Date] >= DATE ( 2022, 1, 1 ),
    WEEKDAY ( [Date], 1 ) IN { 7, 1 },        -- Saturday, Sunday
    IF (
        [Date] >= DATE ( 2006, 9, 1 ),
        WEEKDAY ( [Date], 1 ) IN { 6, 7 },    -- Friday, Saturday
        WEEKDAY ( [Date], 1 ) IN { 5, 6 }     -- Thursday, Friday
    )
)

Sort the names

Select Month Name and choose Column tools → Sort by column → Month Number. Do the same for Day Name with Day of Week, and for Hijri Month Name with Hijri Month Number; each (Arabic) column takes the same number as its English one. Then mark the table as a date table on Date and relate it to your fact table.

Three measures that use it

1. Ramadan day N against last year. The Last Ramadan measure from our Ramadan sales guide moves each Ramadan day in view one Hijri year back. Put Ramadan Day on the axis and day 5 meets day 5.

2. This Ramadan against last Ramadan. On a card filtered to a year, vs Last Ramadan % compares that year's Ramadan days with the same days of the Ramadan before. When a 30-day Ramadan follows a 29-day one, day 30 has no partner and adds nothing to last year's side.

3. The Eid window. If your sales build up before Eid, look at those days together with Eid itself. This measure adds up the 7 days before each Eid al-Fitr and its 3 days, for every Eid whose first day is in view:

Sales Eid al-Fitr Window =
VAR _DaysBefore = 7
VAR _EidDays = 3
VAR _Eids =
    SELECTCOLUMNS (
        FILTER ( 'Calendar', 'Calendar'[Hijri Month Number] = 10 && 'Calendar'[Hijri Day] = 1 ),
        "@Eid", 'Calendar'[Date]
    )
VAR _Window =
    FILTER (
        ALL ( 'Calendar'[Date] ),
        VAR _Day = 'Calendar'[Date]
        RETURN
            NOT ISEMPTY (
                FILTER ( _Eids, _Day >= [@Eid] - _DaysBefore && _Day <= [@Eid] + _EidDays - 1 )
            )
    )
RETURN
    IF (
        NOT ISEMPTY ( _Eids ),
        CALCULATE ( [Total Sales], REMOVEFILTERS ( 'Calendar' ), _Window )
    )

Eid al-Fitr 2025 fell on Sunday 30 March, so its window runs from 23 March to 1 April and belongs to March: a visual by month shows the whole window in March and a blank in April. The DAX Measure Builder writes this measure with your own table and column names, together with the same for Eid al-Adha, last year's window and the % change.

We test these measures on a made-up model (two stores, amounts from a formula, no real data). Its 43 checks, each with the number worked out beforehand, are on GitHub, so you can run them in your own Power BI Desktop.

Three things to watch

  • Your company's weekend. The country options follow the official government weekend. Many private companies keep another one, and Sharjah's government has rested Friday to Sunday since 2022: pick Sharjah government for it. If your weekend is none of the options, pick the closest one and change the days in the Is Weekend and Is Working Day columns' DAX.
  • Update after each announcement, once DataArcus has added it. When Ramadan 2027 is announced, DataArcus adds the date to its table (the sources file shows when). Generate the table again after that, and the announced dates replace the estimates.
  • Other countries' sightings. The announced dates are the UAE's. Saudi Arabia's matched them from 2018 to 2026; Oman announced 11 of them a day later from 2019 to 2026 (each in the sources file), and Qatar, Kuwait and Bahrain can start a day apart. Check the official announcement where you report.

Ramadan 1448 is expected around 8 February 2027. Build the calendar before then, and the day-by-day comparison can start on Ramadan's first day.