Issue with Variation using Time Intelligence when figure is 0

[Sky571]

Hello, 

I have one issue with this formula regarding Variation in values between dates , when the 1st period of variation is 0.

Table 1 :

Result with the issue :

so , in the 2nd row, the variation between 2016 and 2015 should have been +100 instead of blank.

Do you know how to do so with my formulas please :

Here is my formulas :

Calculated column :

D-C = Table1[Debit]-Table1[Credit]

Measures :

Var value =

IF(

    ISFILTERED('Table1'[EndofFiscalYear]),

ERROR("ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy."),

    VAR __PREV_YEAR =

        CALCULATE(

            SUM('Table1'[D-C]),

            DATEADD('Table1'[EndofFiscalYear].[Date], -1, YEAR)

        )

    RETURN

        DIVIDE(SUM('Table1'[D-C]) - __PREV_YEAR, __PREV_YEAR)*__PREV_YEAR

)

Var % =

IF(

    ISFILTERED('Table1'[EndofFiscalYear]),

ERROR("ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy."),

    VAR __PREV_YEAR =

        CALCULATE(

            SUM('Table1'[D-C]),

            DATEADD('Table1'[EndofFiscalYear].[Date], -1, YEAR)

        )

    RETURN

        DIVIDE(SUM('Table1'[D-C]) - __PREV_YEAR, __PREV_YEAR)

)

Thank you in advance

[vivran22]

Hello @Sky571 ,

I have few questions areound the formula you have used. In the Var Value you have used:

DIVIDE( CY - PY, PY)* PY which essentially translate to CY - PY. 

What is you are trying to achieve?

Can you please help me with the expected output and if possible with sample data?

Cheers!
Vivek

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

[Sky571]

Hello @vivran22 ,

Thank you for answering.

I want to keep with Time intelligence because :

• I have Fiscal Years which do not finish at the end of the year
• I want to automatize my model (do not want to change parameters in DAX for that , depending on when the fiscal years end)

The Variation in % works very well with the function DIVIDE. But for the Variation Value => i tried to remove DIVIDE, it works but now I have datas in the 1st year.

See my example :

1st solution with formula DIVIDE : 

pb in the 2nd row when 0 is on the 1st year :

2nd solution with NO formula DIVIDE : 

pb in the column Variation value : figures appears wrongly in the 1st year , but issue resolved concerning 0 in the 1st year

Here is my example : 

https://drive.google.com/file/d/1hy2q9dx8qPkbTs1yBt5EEZHN9I_02lJP/view?usp=sharing 

Thank you !

[vivran22 · Accepted solution]

@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

[Sky571]

Hello @vivran22 ,

Thank you so much ! Yes, it is what I wanted , thank you.

It is just that it needs to add manually the end of the month for the Fiscal Year (in the Date Calendar Table)

so, it is good for users that are able to change the month, according to their customers's End of Fiscal Year.

Thank you so much for your explanation and pbix file ! Greatly appreciated !

xx