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

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-Calendar-1-5-Power/ba-p/1187441
https://medium.com/chandakamit/cheat-sheet-calendar-of-any-standard-month-just-one-variable-apart-5ee47de6a208
https://amitchandak.medium.com/cheat-sheet-power-query-financial-year-calendar-5ceaacb520f1

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!

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.