Creating a KPI in Power BI for FY Basic Amount with Growth Percentage

[PraveenDP]

Hello Community,

I am working on creating a KPI in Power BI that displays the Basic Amount for the current fiscal year (FY 2024) along with the Basic Amount up to the same day of the last fiscal year (FY 2023). Additionally, I want to show the growth percentage between the two values.

Dim_Date Table:
I have created a custom date table (Dim_Date) using the CALENDAR function. This table includes fiscal year columns and other attributes
Dim_Date =
ADDCOLUMNS(
CALENDAR(DATE(2022,04,01),DATE(2026,03,30)),
"Day", DAY([Date]),
"Day Name", FORMAT([Date], "DDDD"),
"Month Number", MONTH([Date]),
"Month Name", FORMAT([Date], "MMMM"),
"FY_Month", MOD(MONTH([Date]) - 4, 12) + 1,
"Quarter_Number",
SWITCH(TRUE(),
MONTH([Date]) IN {4, 5, 6}, 1,
MONTH([Date]) IN {7, 8, 9}, 2,
MONTH([Date]) IN {10, 11, 12}, 3,
MONTH([Date]) IN {1, 2, 3}, 4
),
"FY_Quarter",
"Q" & SWITCH(TRUE(),
MONTH([Date]) IN {4, 5, 6}, 1,
MONTH([Date]) IN {7, 8, 9}, 2,
MONTH([Date]) IN {10, 11, 12}, 3,
MONTH([Date]) IN {1, 2, 3}, 4
),
"Year", YEAR([Date]),
"FY", "FY" & FORMAT(
YEAR([Date]) + IF(MONTH([Date]) >= 4, 0, -1),
"00"
),
"DT", FORMAT([Date], "YYYYMMDD"),
"Fin_Year", YEAR([Date]) + IF(MONTH([Date]) >= 4, 0, -1),
"FY_Trim", TRIM(RIGHT(YEAR([Date]) + IF(MONTH([Date]) >= 4, 0, -1), 2))
)

1. Fact_Invoices Table:
   Columns:

• Invoice Date
• Basic
  
  
  

  Objective:

  I need to calculate the following metrics for the KPI:

  1. This Fiscal Year Basic Amount: Sum of Basic where Invoice Date belongs to FY 2024. and similarly FY 2023,FY 2022 etc...
  2. Last Fiscal Year Basic Amount (Up to the Same Day): Sum of Basic where Invoice Date belongs to FY 2023, up to the same day in the fiscal calendar.
  3. Growth Percentage: ((This FY Basic Amount - Last FY Basic Amount) / Last FY Basic Amount) * 100.

  Could you please help me with the DAX formulas for these metrics and any tips for visualizing this in a KPI card?

  Thank you in advance!

[v-yilong-msft]

Hi @PraveenDP ,

Have you solved your problem? If so, can you share your solution here and mark the correct answer as a standard answer to help other members find it faster?

If that doesn't solve the problem, could you please provide the relevant .pbix file or a link to it?

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[grazitti_sapna]

Hi @PraveenDP,

Let's create a KPI in Power BI to track our Basic Amount progress! We'll compare this year's numbers to the same time last year. To do this, we'll use some special formulas called DAX measures.
Step 1: Identify the Current Fiscal Year
Use the "Dim_Date" table to filter the data by fiscal year. The fiscal year logic is already incorporated in your date table.

Step 2: Create the Metrics

• This Fiscal Year Basic Amount
  This measure calculates the sum of the Basic column for invoices in the current fiscal year.
  
  

  This FY Basic Amount =
  CALCULATE(
  SUM(Fact_Invoices[Basic]),
  Dim_Date[FY] = "FY" & YEAR(TODAY()) + IF(MONTH(TODAY()) >= 4, 0, -1) -- Current FY logic
  )

• Last Fiscal Year Basic Amount (Up to the Same Day)
  This measure calculates the sum of the Basic column for the same period in the previous fiscal year (up to today's date).
  
  

  Last FY Basic Amount =
  CALCULATE(
  SUM(Fact_Invoices[Basic]),
  Dim_Date[FY] = "FY" & YEAR(TODAY()) - 1 + IF(MONTH(TODAY()) >= 4, 0, -1), -- Last FY logic
  Dim_Date[Day of Fiscal Year] <= 
  MAXX(
  FILTER(
  Dim_Date, 
  Dim_Date[FY] = "FY" & YEAR(TODAY()) + IF(MONTH(TODAY()) >= 4, 0, -1)
  ), 
  Dim_Date[Day of Fiscal Year]
  ) -- Same day comparison
  )

• Explanation:

• Dim_Date[FY] filters the last fiscal year.

• The Day of Fiscal Year is used to ensure that only dates up to today in the fiscal calendar are included for comparison. If your Dim_Date table doesn't have a Day of Fiscal Year column, you can calculate it as:

DAX:
Day of Fiscal Year = DATEDIFF(
DATE(YEAR(Dim_Date[Date]), 4, 1), -- Start of the fiscal year
Dim_Date[Date],
DAY
) + 1

• Growth Percentage
  This measure calculates the percentage growth between the current fiscal year and the last fiscal year.
  

  Growth Percentage =
  IF(
  [Last FY Basic Amount] > 0,
  DIVIDE(
  [This FY Basic Amount] - [Last FY Basic Amount],
  [Last FY Basic Amount],
  0
  ) * 100,
  BLANK()
  
  )

Step 3: Visualizing the KPI
To display the KPI, you can use a KPI visual in Power BI:

• Add the KPI visual to your report canvas.
• Set the Indicator field to [This FY Basic Amount].
• Set the Target Goals field to [Last FY Basic Amount].
• Add a trend axis if needed, for example, using Dim_Date[Date] or Dim_Date[Month Name].
• To show the growth percentage, create a Card visual and display the [Growth Percentage] measure.

Tips for Visualization

• Use color formatting to indicate positive or negative growth:
  
  • Green for positive growth.
  • Red for negative growth.

I Hope this Works

If I have resolved your question, please consider marking my post as a solution. Thank you!

[PraveenDP]

This dax not working

[grazitti_sapna]

Hi @PraveenDP ,
I hope you're doing well. Could you please share a screenshot or explain the challenges you're encountering? We can work together to find a solution

[amitchandak]

@PraveenDP , If you need Fin Year till date

example

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"03/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"03/31"))

Time Intelligence, Part of learn Power BI https://youtu.be/cN8AO3_vmlY?t=27510
Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s

Creating Financial Calendar - From Any Month
https://community.powerbi.com/t5/Community-Blog/Creating-Financial-Calendar-Decoding-Date-and-Calend...
https://medium.com/chandakamit/cheat-sheet-calendar-of-any-standard-month-just-one-variable-apart-5e...
https://amitchandak.medium.com/cheat-sheet-power-query-financial-year-calendar-5ceaacb520f1