Hi @mail2rohitnagar ,
To achieve your desired calculation for a corrected target in Power BI, you should use DAX measures rather than calculated columns. This approach is more efficient and flexible because the calculation is iterative and depends on values from previous time periods. Measures are evaluated dynamically based on the context of your report visuals, making them ideal for this type of time-based analysis.
First, it is essential to have a proper date table in your model, as time intelligence functions rely on it. If you do not have one, you can create a new table using the following DAX expression. You should adjust the start and end dates to fit your data. After creating the table, right-click on it in the Fields pane and select "Mark as date table," choosing the [Date] column.
DateTable =
ADDCOLUMNS (
CALENDAR ( DATE ( 2025, 1, 1 ), DATE ( 2026, 12, 31 ) ),
"Year", YEAR ( [Date] ),
"MonthNumber", MONTH ( [Date] ),
"MonthName", FORMAT ( [Date], "mmmm" ),
"YearMonth", FORMAT ( [Date], "yyyy-mm" )
)
Your main data table, which we can call SalesData, will need a Date column to relate to this new DateTable. You can add this as a calculated column in your SalesData table. Then, in the Model view, create a one-to-many relationship between DateTable[Date] and SalesData[Date].
Date = DATE('SalesData'[Year], 'SalesData'[Month], 1)
With the model prepared, you can create the necessary measures. It is good practice to start with explicit base measures for your core values.
Total Target = SUM('SalesData'[Target (Cr)])
Total Actual Sales = SUM('SalesData'[Actual Sales(cr)])
The logic for your Variance is a running total of the difference between the target and actual sales over time. The following measure calculates this cumulative value. It works by summing the [Total Target] - [Total Actual Sales] difference over a modified date context that includes all dates up to the last date of the current period being evaluated in your visual.
Variance (Cr) =
VAR FirstDateInContext =
CALCULATE ( MIN ( 'SalesData'[Date] ), ALLSELECTED ( 'SalesData' ) )
RETURN
CALCULATE (
[Total Target] - [Total Actual Sales],
FILTER (
ALLSELECTED ( 'DateTable' ),
'DateTable'[Date] >= FirstDateInContext
&& 'DateTable'[Date] <= MAX ( 'DateTable'[Date] )
)
)
Finally, the Corrected Target measure can be created. This formula calculates the Variance (Cr) from the previous month and adds it to the current month's Total Target. It uses the DATEADD function to shift the calculation context back one month. The COALESCE function is important for handling the very first period, ensuring that a missing previous month's variance is treated as zero.
Corrected Target (Cr) =
VAR PreviousMonthVariance =
CALCULATE (
[Variance (Cr)],
DATEADD ( 'DateTable'[Date], -1, MONTH )
)
RETURN
IF (
NOT ISBLANK ( [Total Target] ),
[Total Target] + COALESCE ( PreviousMonthVariance, 0 )
)
To display the results, you can use a Matrix visual in your Power BI report. Place fields like Sales Person Name, Particular, and YearMonth from the DateTable onto the rows. Then, add your measures—Total Target, Total Actual Sales, Corrected Target (Cr), and Variance (Cr)—to the values area to generate the final table.
Best regards,
