Islamic Calendar in Power Query and Power BI?

Essam AbdulFattah asked me to help him convert Gregorian to Hijri in Power Query. It is extremely relevant for all Muslims and also for companies dealing with the Islamic world and I learned a lot from this.

Why is it important: For companies in the Islamic world or in countries with an important Muslim community an analysis of their business activity according to the Hijri calendar makes a lot of sense. E.g. they might make special offers during the holy month of Ramadan and want to analyze the impact on the sales of this month.

Problem and solution: First off you cannot change the date format in Power Query, but what you can do is to import a Hijri calendar table which you create in Excel. And in Excel it is relatively easy to create Date columns with different date formatting.

The imported Hijri calendar table you can either merge with your standard calendar in Power Query or create a relation in the Power BI report.

How to do it and what to watch for:

Create a column of the Gregorian dates [Gregorian] you are interested in [I’d do this with Power Query].

Don’t just copy that column and change the format in Excel! Instead use for example = TEXT([@Gregorian], “B2dd/mm/yyyy”)?[In Europe use semicolon instead of comma e.g. = TEXT([@Gregorian]; “B2TT.MM.JJJJ”)]

By this the Hijri date will be a text which Power Query will import as text and won’t automatically translate it back to a Gregorian date.

Add also the Hijri year, the Hijri month, the Hijri month name and the Hijri day. You can also add further columns like a Hijri (YY)YY-MM column.

Options:

1. Change the day, month, year order and the separators according to your needs.

2. Of course you can also go for mmmm to get the full month name in the Hijri date (changes the writing direction ? "B2dd mmmm yyyy" to get e.g. yyyy ??? dd).

3. For specific locales and calendars add IDs, e.g. for Saudi Arabia Umm-Al-Qura = TEXT([@Gregorian], “[$-,117]B2dd/mm/yyyy”)

Why is there more than one Islamic Calendar?

The Islamic calendar depends on the movement of the moon. The beginning of each month is marked by the observance of a new moon seen for the first time. Visibility of the new moon depends on various factors such as the weather. What was a surprise to me, were the many astrological and theological aspects and even controversies (actual moon sighting vs. pre-calculated) and that they are handled differently in different countries. "B2" gives you the Saudia Arabia Hijri calendar. And Hijri dates ≠ Umm-Al-Qura dates.

How to get the definition of the locales and calendar you are interested in?

1. Somewhere on a separate cell open Format Cells (CTRL +1).
2. Select Date.
3. Select the Locale (location) you need.
4. Select the Calendar type you need.
5. Press OK.

1. On the same cell reopen Format Cells (CTRL +1).
2. Select Custom.
3. At the beginning of the code you will see the ID.
4. Copy it out from the space where you can adapt custom formats.
5. Bring it to your TEXT format formula.

Use calendar to convert dates:

Apart from the use for business intelligence you can use the created calendar also for date conversion. Here is an example with slicers implemented by Essam AbdulFattah:

Interesting Excel date trivia (which you normally don’t need):

For compatibility with Lotus 1-2-3 Microsoft Excel has, since its earliest versions, incorrectly considered 1900 to be a leap year, and therefore February 29 comes between February 28 and March 1 of that year. However, 1900 was not a leap year. This has the side effect that for the period Jan 01 1900 to Feb 28 1900, the WEEKDAY function reports incorrect values.

Interestingly enough Power BI / Power Query do not include February 29, 1900 and therefore the dates start at December 31, 1899 and when you load it to Excel it is shown as the nonexistent January 0, 1900. ??

=> The first 60 dates are offset by one day between Excel and Power BI / Power Query.

What Wikipedia told me:

The?Hijri calendar?(Arabic:???????????? ????????????at-taqwīm al-hijrīy), also known as the?Lunar Hijri?calendar and (in English) as the?Islamic,?Muslim?or?Arabic calendar, is a?lunar calendar?consisting of 12?lunar months?in a year of 354 or 355 days. It is used to determine the proper days of?Islamic holidays?and rituals, such as the?annual period of fasting?and the proper time for the?Hajj. [...]

As the mean duration of a?solar year?is 365.24219 days, while the long-term average duration of a?synodic month?is 29.530587981 days, the average lunar year is (365.24219 ? 12 × 29.530587981 ≈) 10.87513 days shorter than the average solar year, causing months of the Hijri calendar to advance about eleven days earlier relative to dates in the Gregorian calendar every calendar year. As a result, the cycle of twelve lunar months regresses through the seasons over a period of about 33 solar years. [...]

New: Use Date.ToText plus Format AND Culture to control Date Conversion!

Here is Date.ToText with format and culture in square brackets:

= Table.AddColumn( prevStep, "MMM YYYY", each Date.ToText([Month], [Format="MMM yyyy", Culture="en-US"]))

It makes it very clear what it is doing, but it is also possible to go without the square brackets. Less fascinating, but it works the same:

= Table.AddColumn( prevStep, "MMM YYYY", each Date.ToText([Month],

"MMM yyyy", "en-US"))

With the format portion you can freely design your date exactly as you want it:

"dddd, dd.MMMM yyyy" => Sunday, 01.January 2023

The culture portion controls some format specifiers. For example, with?"en-US"?"MMM"?is?"Jan", "Feb", "Mar", "Apr", "May", ..., while with?"de-DE"?"MMM"?is?"Jan", "Feb", "Mrz", "Apr", "Mai", ...,

That also works for the Hijri calendar. With the culture code for Saudi Arabia you can get for example the year in the Hijri calendar like this:

= Table.AddColumn( prevStep, "Hijri Year", each Date.ToText([Month], "yyyy", "ar-SA"))

And the full Hijri month name like this:

= Table.AddColumn( prevStep, "Hijri Month", each Date.ToText([Month],?"MMMM", "ar-SA"))

So this is an excellent alternative or complement to the Excel based Hijri solution!

Questions and suggestions: https://www.dhirubhai.net/in/matthiasfriedmann

?Let?me?help?you?with?your?data!

"LIKE", "COMMENT" or "SHARE" this article,?to give your network a chance to find it!