Forum Discussion

Marieamelll's avatar
Marieamelll
Regular Visitor
3 years ago
Solved

YTD and FY

Dear All,

I'm working on a dashboard, with monthly spends.

I'd like to have a chart with the last 3 FY and one with the last 3 YTD.

For example, one with data from Jan to Nov, 2020, 2021 and 2022, and one, on the same page, with FY 2019, 2020 and 2021. But I'm not sure to have the easiest way to filter on the last 3 FY.

If you could help me with the best way to build this, knowing that it needs to be easy to use and update by people from my company.

Thanks in advance,

Kind regards,

MA

Need Help
  • hummingbird's avatar
    hummingbird
    3 years ago

    Hi,

     

    To build a dashboard with monthly spends for the last 3 fiscal years and the last 3 years to date, you can use the following steps:

    1. Create a date table: First, you need to create a date table in Power BI. The date table will be used to create a relationship between the spend data and the date.

    2. Load the spend data: Load the spend data into Power BI, including the date and spend amounts. Make sure the date column is formatted as a date.

    3. Create a fiscal year column: Create a fiscal year column in the spend data table, which represents the fiscal year for each spend transaction. You can do this by using the DAX formula =YEAR(date) + IF(MONTH(date)>=7, 1, 0).

    4. Create relationships: Create a relationship between the date table and the spend data table, using the date column as the link.

    5. Create a calculated column: Create a calculated column in the spend data table to calculate the year-to-date spend for each transaction. You can do this by using the DAX formula =CALCULATE(SUM(spend), DATESYTD(dates[date]))

    6. Create the charts: In the report view, create two charts, one for the last 3 fiscal years and one for the last 3 years to date. You can do this by using the fiscal year and year-to-date calculated columns as the basis for your charts.

    7. Add filters: Add filters to the charts to display only the last 3 fiscal years and last 3 years to date. You can do this by using the fiscal year and year-to-date calculated columns as the basis for your filters.

3 Replies

  • hummingbird's avatar
    hummingbird
    Icon for Helper II rankHelper II
    3 years ago

    Hi,

     

    To build a dashboard with monthly spends for the last 3 fiscal years and the last 3 years to date, you can use the following steps:

    1. Create a date table: First, you need to create a date table in Power BI. The date table will be used to create a relationship between the spend data and the date.

    2. Load the spend data: Load the spend data into Power BI, including the date and spend amounts. Make sure the date column is formatted as a date.

    3. Create a fiscal year column: Create a fiscal year column in the spend data table, which represents the fiscal year for each spend transaction. You can do this by using the DAX formula =YEAR(date) + IF(MONTH(date)>=7, 1, 0).

    4. Create relationships: Create a relationship between the date table and the spend data table, using the date column as the link.

    5. Create a calculated column: Create a calculated column in the spend data table to calculate the year-to-date spend for each transaction. You can do this by using the DAX formula =CALCULATE(SUM(spend), DATESYTD(dates[date]))

    6. Create the charts: In the report view, create two charts, one for the last 3 fiscal years and one for the last 3 years to date. You can do this by using the fiscal year and year-to-date calculated columns as the basis for your charts.

    7. Add filters: Add filters to the charts to display only the last 3 fiscal years and last 3 years to date. You can do this by using the fiscal year and year-to-date calculated columns as the basis for your filters.

    • hummingbird's avatar
      hummingbird
      Icon for Helper II rankHelper II
      3 years ago

      Marieamelll no worries at all, happy to help and appreciate your Kudos and if could mark it as a solution.