How to create a Indian Fiscal Year Calendar Table in Power BI

 If you are someone who works with Fiscal Calendar that does not start with Jan and end in December, you are going to love this post. In this post, I’ll teach you how can you calculate Custom Fiscal Year in Power BI.

And since I am based in India my examples will be with dates as per Indian Financial Calendar which starts in Apr and ends in Mar. You may modify the formulas to suit your need

How to create a Fiscal Year Calendar in Power BI

Using almost the same logic for year and quarter along with a few other columns here is the Pre-Built DAX Code that creates a Calendar Table

Calendar =

--Inputs--
VAR WeekStartsOn = "Mon"
VAR FiscalStartMonth = 4 

--NOTE: Calendar week starts from Monday

--Calculation--
RETURN
    ADDCOLUMNS (
        CALENDARAUTO ( FiscalStartMonth - 1 ),
        "MIndex", MONTH ( [Date] ),
        "FiscalMIndex", MONTH ( EDATE ( [Date], - FiscalStartMonth + 1 ) ),
        "CalMonth", FORMAT ( [Date], "mmm" ),
        "CalQtr", "Q"
            & CEILING ( MONTH ( [Date] ), FiscalStartMonth - 1 ) / ( FiscalStartMonth - 1 ),
        "CalYear", YEAR ( [Date] ),
        "Fiscal Week",
        VAR FiscalFirstDay =
            IF (
                MONTH ( [Date] ) < FiscalStartMonth,
                DATE ( YEAR ( [Date] ) - 1, FiscalStartMonth, 1 ),
                DATE ( YEAR ( [Date] ), FiscalStartMonth, 1 )
            )
        VAR FilteredTableCount =
            COUNTROWS (
                FILTER (
                    SELECTCOLUMNS ( GENERATESERIES ( FiscalFirstDay, [Date] ), "Dates", [Value] ),
                    FORMAT ( [Dates], "ddd" ) = WeekStartsOn
                )
            )
        VAR WeekNos =
            IF (
                FORMAT ( FiscalFirstDay, "ddd" ) <> WeekStartsOn,
                FilteredTableCount + 1,
                FilteredTableCount
            )
        RETURN
            "Week " & WeekNos,
        "Fiscal Qtr", "Q"
            & CEILING ( MONTH ( EDATE ( [Date], - FiscalStartMonth + 1 ) ), 3 ) / 3,
        "Fiscal Year",
        VAR CY =
            RIGHT ( YEAR ( [Date] ), 2 )
        VAR NY =
            RIGHT ( YEAR ( [Date] ) + 1, 2 )
        VAR PY =
            RIGHT ( YEAR ( [Date] ) - 1, 2 )
        VAR FinYear =
            IF ( MONTH ( [Date] ) > ( FiscalStartMonth - 1 ), CY & "-" & NY, PY & "-" & CY )
        RETURN
            FinYear,
        "CalWeekNo", WEEKNUM ( [Date], 2 ),
        "Weekend/Working", IF ( WEEKDAY ( [Date], 2 ) > 5, "Weekend", "Working" ),
        "Day", FORMAT ( [Date], "ddd" ),
        "CustomDate", FORMAT ( [Date], "d/mm" )
    )

How to use this DAX Code

You can simply copy and paste the code in Power BI. To be precisely follow the steps

Open one of your existing Power-BI file (with some data)

  1. Go to Modeling Tab
  2. Click on New Table
  3. Paste the code and a new Cal table will be created with 12 columns (FY and Financial Qtrs being the 2 of them)

Resulting Calendar Table in your Power BI would look like this

Things to check –

  • Just be sure to load some data with one of the columns as the date in the Power BI file before you paste the code. Rest will fall in place automatically
  • And do create a relationship between the two tables.

Comments

Post a Comment

Popular posts from this blog

Understanding different Loss Functions for Neural Networks

Image
  Understanding different Loss Functions for Neural Networks Image by  KotaFeja The  Loss   Function  is one of the important components of Neural Networks.  Loss  is nothing but a prediction error of Neural Net. And the method to calculate the loss is called Loss Function. In simple words, the Loss is used to calculate the gradients. And gradients are used to update the weights of the Neural Net. This is how a Neural Net is trained. Keras  and  Tensorflow  have various inbuilt loss functions for different objectives. In this guide, I will be covering the following essential loss functions, which could be used for most of the objectives. Mean Squared Error (MSE) Binary Crossentropy (BCE) Categorical Crossentropy (CC) Sparse Categorical Crossentropy (SCC) Mean Squared Error MSE  loss is used for regression tasks. As the name suggests, this loss is calculated by taking the mean of squared differences between actual(target) and predict...
Read more

What is Gini Impurity?

Image
  Gini Impurity A measurement used to build Decision Trees to determine how the features of a dataset should split nodes to form the tree. What is Gini Impurity? Gini Impurity is a measurement used to build Decision Trees to determine how the features of a dataset should split nodes to form the tree. More precisely, the Gini Impurity of a dataset is a number between 0-0.5, which indicates the likelihood of new, random data being misclassified if it were given a random class label according to the class distribution in the dataset. For example, say you want to build a classifier that determines if someone will default on their credit card. You have some labeled data with features, such as bins for age, income, credit rating, and whether or not each person is a student. To find the best feature for the first split of the tree – the root node – you could calculate how poorly each feature divided the data into the correct class, default ("yes") or didn't default ("no...
Read more

Optimization Algorithms in Neural Networks

Image
  Optimization Algorithms in Neural  Networks This article presents an overview of some of the most used optimizers while training a neural network. Introduction   In deep learning, we have the concept of loss, which tells us how poorly the model is performing at that current instant. Now we need to use this loss to  train  our network such that it performs better. Essentially what we need to do is to take the loss and try to  minimize  it, because a lower loss means our model is going to perform better. The process of minimizing (or maximizing) any mathematical expression is called  optimization. Optimizers are algorithms or methods used to change the attributes of the neural network such as  weights  and  learning rate  to reduce the losses. Optimizers are used to solve optimization problems by minimizing the function.  How do Optimizers work?  For a useful mental model, you can think of a hiker trying to get down a...
Read more