Tracking year-over-year performance is a common requirement in business reporting. But, maintaining formulas that rely on hardcoded year values can become cumbersome as data evolves over time. In scenarios where the reporting period changes regularly, a dynamic approach becomes essential.

In this blog, we’ll walk you through a simple example of how to calculate the variance between the Current Year and Previous Year in Inforiver dynamically, making your reports more scalable, efficient and easier to maintain.

For instance, let’s start by calculating the monthly Profit variance between 2024 and 2025 by inserting a Template row.

To do this, select any row under January month, then navigate to the Insert tab --> Insert Row --> Template Row.

The Template Rows feature allows you to automatically insert rows across all hierarchy levels in a single step, eliminating the need to add them individually as illustrated in the above screenshot.

Next, let’s create a dynamic formula to calculate the variance between the two years.

ROW.PARENT.MEMBERS[1] - ROW.PARENT.MEMBERS[0]

The above formula dynamically calculates the variance between members within the parent hierarchy (Months), specifically comparing values for the years 2025 and 2024. Since the calculation is dynamic, there is no need for manual updates when the year values change.

As demonstrated in the below screenshot, even when the dataset shifts to years like 2026 and 2025, the formula continues to work as expected, seamlessly adapting to the available data and consistently delivering accurate results without any additional adjustments.

To know more about ROW.PARENT identifier, click here.

Conditional formatting is often used to highlight high or low values. Instead of using static thresholds, you can take this a step further by creating smart, dynamic rules. These rules compare your data against benchmarks that update automatically based on filter selections, giving you a more accurate and context-aware highlights every time the data changes.

Let’s look at a simple use case.

Imagine you have a report that displays Cost, Revenue, Margin% and Average sales for each product. Now, you want to highlight the products whose Average sales exceed the Total Average Sales in green and show the remaining values in red. You want this comparison to update automatically whenever a user changes the month filter.

To achieve this, follow the below steps.

1. Go to Home tab -> Conditional formatting -> click on Create rule.

2. In the ‘Apply to’ field select the measure that you would like to highlight.

3. Under Format by choose Rules (If Condition).

4. In the Conditions section, configure the rule by setting Average sales to Greater than or equal to, and choose Data selection from the dropdown. This option allows you to use a cell value in the condition.

5. Next, click inside the Select value from field and choose the Grand total cell that corresponds to Average Sales in your report.  The selected cell value will automatically populate in the rule configuration.

6. I have also set the highlight text color to Green.

7. Click on ‘Apply’

As shown in the screenshot below, for the month of January, any product whose Average Sales value exceeds the Grand Total value is highlighted in green. In this example, Home Appliances and TV and Video are highlighted.

Similarly, let’s create another dynamic conditional formatting rule to highlight the Average Sales values in red when they fall below the Grand Total value.

As shown in the screenshot below, the values that fall below Grand Total are now highlighted in red.

Now, when the Month filter is changed from January to December, the values are dynamically highlighted based on the updated Grand Total cell value, as shown in the screenshot below.

Thus, incorporating dynamic conditional formatting not only enhances visual clarity but also ensures that insights remain relevant as users interact with filters in the report.

Frequently asked questions:

Is it possible to apply conditional formatting based on a selection during runtime?

Yes, it is possible. In the Conditions section, configure the rule by selecting ‘User selection’ from the dropdown, as shown below.

You can find more details in our User Guide here.

Understanding trends, patterns, and performance over time within an organization’s data is a vital component of data analysis.

How to use Time Intelligence functions in Power BI

YTD – Year to Date

The Year-To-Date calculation is fundamental for tracking cumulative performance over the course of a year. It aggregates a metric from the start of the year up to a specified date. It is useful for analyzing metrics such as sales, revenue, or any other metric within the year, especially to track the cumulative performance over time.

To calculate YTD sales, we sum up the sales from January 1st to the Max date. We use the DAX Expression – TOTALYTD. Here is an example,

Example: Below shows Actual values from Jan to December.

Once you apply PowerBI TOTALYTD DAX, you will get the cumulative sum for the Actual value.

Inforiver enhances the capabilities of Microsoft Power BI through its advanced visual features.

The TOTALYTD function in Inforiver calculates the year-to-date YTD value of a measure within the current filter context (similar to Power BI’s TOTALYTD function). It should be used together with an aggregation function such as SUM or AVERAGE.

Inforiver also provides a YTD function that retrieves data from a specified measure for the current year. If an offset parameter is supplied, the function can return values from previous years as well.

For example, if the current year is 2026 and the current month is March, YTD will return the aggregated total from January through March, as illustrated below.

With an offset of 1, the YTD function returns data for the same period in the previous year.

For example, if the current year is 2026 and the month is March, applying an offset of 1 will return the cumulative total from January through March 2025.

Similarly, Inforiver also provides TOTALMTD, TOTALQTD, MTD, and QTD functions to calculate month-to-date and quarter-to-date values, offering flexible time-based analysis within the current context.

For more information, refer to Time-Intelligence Functions

Frequently Asked Questions

1.      Difference between YTD and TOTALYTD

Ans.Inforiver TOTALYTD works the same way as the TOTALYTD function in Microsoft Power BI. It returns a cumulative running total that evaluates the year-to-date value of an expression, calculating the total from the start of the year up to the maximum date in the current context.

In contrast, YTD in Inforiver computes a single aggregated value for the specified period, rather than producing a running cumulative total.

Calculation groups can significantly reduce the number of redundant measures by grouping common measure expressions as calculation items.

Steps to create and use Calculation Group

1.      Create a Calculation Group in Power BI

Use case:

We have TotalSales and TotalQty in the model and need to build time intelligence calculations for Previous Year (PrevYear), Month-to-Date (MTD), and Year-to-Date (YTD).

Using the traditional approach, you would typically create three separate DAX measures for each metric (TotalSales and TotalQty), resulting in multiple individual measures.

Instead, we can simplify the model by creating a single Calculation Group that handles PrevYear, MTD, and YTD calculations for all relevant measures, as illustrated below.

2.      Now this Calculation group can be integrated into Superfilter

For more information about SuperFilter, refer Introduction to Inforiver Super Filter | Super Filter | Inforiver

and for Calculation Group , refer Calculation groups in Analysis Services tabular models | Microsoft Learn

When using URL Links in our dataset a lot of times we do not want to display the URL links column in our report as shown below:

Instead, we only want to display the Hyperlink Icon Column as shown in PBI Table as this makes our report look cleaner and more concise:

We can achieve something similar in Inforiver Reporting Matrix/Writeback Matrix/Premium Table visuals.

1) First, we have to set our URL link column as Text (Format) and Web URL (Data Category) as shown below:

2) Secondly make sure that the URL link column is located under Values (AC) Field:

3) Next let us disable the Enable Canvas Rendering option under Display tab so that we can add the Hyperlink icons:

4) To display only an icon for the URL column, we first need to create an Insert Formula measure and reference the URL text column within that measure:

Next to get the URL icon, first we add a single quotation mark ('') and within that single quotation mark ('') press the Windows key + Semi colon key (;) and select the Hyperlink icon from the given options:

5) Finally, when we click Create, we can see our Hyperlink Icon column:

6) If needed we can hide our original URL text column by using the Hide feature so that we only display the Hyperlink Icon column:

FAQs:

Can we add other icons?

Yes, we can add other icons.

Is this feature available in Analytics+ visual?

No, it isn't.