YTD and FY

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.