6 Replies to “How to Calculate YTD (Year to Date) Values in Power BI”

1. I have a trouble here, I am trying to calculate the YTD of a Yield, but I calculated the Yield using two columns of data from different tables using a measure, but now I can’t seem to calculate the YTD of that Yield measure.

   Reply

   1. Hi Michelle…Calculating the Year-to-Date (YTD) of a measure that involves data from multiple tables can be tricky, but it’s definitely possible with DAX (Data Analysis Expressions) in Power BI. Here’s a step-by-step approach to help you achieve this:

      ### Step-by-Step Approach

      1. **Ensure Relationships**:
      – Make sure that the tables involved have proper relationships set up in the data model. Usually, a date table is linked to your fact tables.

      2. **Create a Date Table**:
      – If you don’t have a date table, create one. Power BI requires a date table to use time intelligence functions.

      “`DAX
      DateTable = CALENDAR(MIN(‘YourTable'[DateColumn]), MAX(‘YourTable'[DateColumn]))
      “`

      3. **Mark as Date Table**:
      – Mark this table as the date table. Go to the “Modeling” tab, select “Mark as Date Table” and specify the date column.

      4. **Calculate the Yield**:
      – Assuming you have already calculated the Yield measure, let’s denote it as `YieldMeasure`.

      “`DAX
      YieldMeasure =
      DIVIDE(
      SUM(‘Table1′[NumeratorColumn]),
      SUM(‘Table2′[DenominatorColumn])
      )
      “`

      5. **Calculate the YTD Yield**:
      – Use the `TOTALYTD` function to calculate the Year-to-Date value of your Yield measure.

      “`DAX
      YTD Yield =
      TOTALYTD(
      [YieldMeasure],
      ‘DateTable'[DateColumn]
      )
      “`

      ### Detailed Example

      Let’s consider an example with specific tables and columns.

      #### Tables
      – **Table1**: Has columns `Date`, `Sales`.
      – **Table2**: Has columns `Date`, `Units`.

      #### Steps

      1. **Create a Date Table**:

      “`DAX
      DateTable = CALENDAR(MIN(‘Table1′[Date]), MAX(‘Table1′[Date]))
      “`

      2. **Mark as Date Table**:
      – Go to “Modeling” -> “Mark as Date Table” -> Select `DateTable` and its `Date` column.

      3. **Create Yield Measure**:

      “`DAX
      YieldMeasure =
      DIVIDE(
      SUM(‘Table1′[Sales]),
      SUM(‘Table2′[Units])
      )
      “`

      4. **Calculate YTD Yield**:

      “`DAX
      YTD Yield =
      TOTALYTD(
      [YieldMeasure],
      ‘DateTable'[Date]
      )
      “`

      ### Considerations

      – **Filter Context**: Ensure the date table is filtering your fact tables correctly.
      – **Data Granularity**: Make sure your date table and fact tables are at the same granularity (e.g., daily).
      – **Date Ranges**: The date ranges in your date table should cover the dates in your fact tables.

      ### Troubleshooting

      – **Relationships**: Check if all necessary relationships between tables are active.
      – **Date Table Issues**: Ensure the date table is properly marked and spans the entire date range of your data.
      – **Measure Validity**: Validate that `YieldMeasure` works correctly on its own before applying `TOTALYTD`.

      ### Summary

      1. Create and mark a date table.
      2. Ensure relationships are correctly set up.
      3. Define the yield measure.
      4. Calculate the YTD using `TOTALYTD`.

      This approach should help you calculate the YTD for your yield measure in Power BI. If you encounter any specific errors or issues, please share them for more targeted assistance.

      Reply

2. How to calculate sum if the date traverse years?
   Currently I have a data that I sum up to the current date. TOTALYTD works fine but once it gets to January, I can’t use it anymore. Example I want to sum from Jan 2024 to Jan 2025.

   Reply

   1. Hi Vin…To calculate **Year-to-Date (YTD)** values that **span multiple years** in Power BI, you’ll need to adjust your DAX formula beyond `TOTALYTD`. Here are a few solutions to ensure that your YTD calculation works across multiple years.

      —

      ### **Solution 1: Use DATESYTD with Custom End Date**
      The `TOTALYTD` function works for the **current year** but resets in January. You can use the `DATESYTD` function and set a custom **end date** to extend the YTD calculation across years.

      #### DAX Formula:
      “`DAX
      YTD_Sum =
      CALCULATE(
      SUM(‘YourTable'[Value]),
      DATESYTD(‘YourTable'[Date], “01/31/2025”) — Adjust the end date as needed
      )
      “`

      **Explanation:**
      – `DATESYTD` calculates YTD for the range of dates up to the specified date, in this case, **January 31, 2025**.
      – You can dynamically adjust this date if needed using `TODAY()` or a parameter from your report.

      —

      ### **Solution 2: Custom YTD with Date Filter Spanning Multiple Years**
      If you need more flexibility (e.g., spanning fiscal years), use a date filter within a `CALCULATE` function.

      #### DAX Formula:
      “`DAX
      YTD_Sum =
      CALCULATE(
      SUM(‘YourTable'[Value]),
      ‘YourTable'[Date] >= DATE(2024, 1, 1) && ‘YourTable'[Date] <= TODAY() ) ``` **Explanation:** - This formula sums the values from **January 1, 2024**, up to the **current date**. - You can adjust the date boundaries if needed. --- ### **Solution 3: Creating a Custom Calendar for Non-Standard YTD** If you are using a **custom fiscal year** (e.g., spanning from July to June), create a custom date range calculation. #### DAX Formula: ```DAX YTD_Sum_Fiscal = CALCULATE( SUM('YourTable'[Value]), 'YourTable'[Date] >= DATE(2024, 1, 1) &&
      ‘YourTable'[Date] <= DATE(2025, 1, 31) ) ``` **Explanation:** - This formula calculates the sum between **January 2024 to January 2025**. - You can dynamically set the end date using `TODAY()` or other report parameters. --- ### **Solution 4: Rolling 12-Month or Custom Period Sum** If you want a **rolling 12-month sum** or a sum that dynamically adjusts, this approach ensures that only the relevant period is summed. #### DAX Formula: ```DAX Rolling_12M_Sum = CALCULATE( SUM('YourTable'[Value]), DATESINPERIOD( 'YourTable'[Date], TODAY(), -12, MONTH ) ) ``` **Explanation:** - `DATESINPERIOD` allows summing values over a **custom period**, here the **last 12 months** ending at the current date. --- ### **Conclusion:** - Use `DATESYTD` with a custom end date if you need a continuous YTD calculation spanning multiple years. - Use `CALCULATE` with conditional filtering for flexible custom ranges (like fiscal years or rolling periods). These approaches will allow you to compute YTD values that extend across years and accommodate custom date ranges.

      Reply

3. Have you adjusted this formula to run in January when its a new year. In example when I run my reports in Jan 2026, I don’t want 2026 YTD. I want 2025 YTD data. So the data should be Jan-Dec 2025. Basically Year to Date the Previous Month.

   Reply

   1. Hi Kris…we have not tried that. Have you worked on it?

      Reply