Issue with Variation using Time Intelligence when figure is 0

Sky571 ,

From what I have understood your scenario, I have recreated it with some dummy values:

Data table:

For using the time intelligence in Power BI, adding a date calendar is recommended. I have used the following DAX code for adding new DAX table:

ftCalendar = 
ADDCOLUMNS (
    CALENDARAUTO (),
    "Year", YEAR ( [Date] ),
    "Month", EOMONTH ( [Date], -1 ) + 1,
    "Qtr", "Q" & FORMAT ( [Date], "q" ),
    "FY", IF (
        //for financial year ending in March
        MONTH ( [Date] ) > 3,
        "FY " & YEAR ( [Date] ) & "-"
            & RIGHT ( YEAR ( [Date] ) + 1, 2 ),
        "FY "
            & YEAR ( [Date] ) - 1 & "-"
            & RIGHT ( YEAR ( [Date] ), 2 )
    ),
    "FY Quarter", "Q"
        & CEILING ( MONTH ( EOMONTH ( [Date], -3 ) ), 3 ) / 3
)

In this, I have included two columns for Financial year and FY quarter. In this example, the FY ends in March.

For more details on this, you may refer to the following articles:

https://www.vivran.in/post/calculating-financial-year-quarter

https://www.vivran.in/post/power-bi-time-intelligence-calendar-table

Mark the table as Date table and create the relationship between the data table and the calendar table:

Balance = SUM(dtTable[Debit]) - SUM(dtTable[Credit])

Balance Prev Month = CALCULATE([Balance],PREVIOUSMONTH(ftCalendar[Date]))

Prev Year Balance = CALCULATE([Balance],SAMEPERIODLASTYEAR(ftCalendar[Date]))

%Variance PY = DIVIDE([Prev Year Balance] - [Balance],[Prev Year Balance])

% Variation PM = DIVIDE([Balance Prev Month] - [Balance],[Balance Prev Month])

Then you can use it in the visuals accordingly:

You may refer to the folowing articles for more details:

https://www.vivran.in/post/introduction-to-time-intelligence-part-1

https://www.vivran.in/post/dax-time-intelligence-part-2-till-date-aggregations

Cheers!
Vivek

If it helps, please mark it as a solution. Kudos would be a cherry on the top 🙂
If it doesn't, then please share a sample data along with the expected results (preferably an excel file and not an image)

Blog: vivran.in/my-blog
Connect on LinkedIn
Follow on Twitter