Forum Discussion

Sky571's avatar
Sky571
Advocate I
6 years ago
Solved

Issue with Variation using Time Intelligence when figure is 0

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's avatar
    vivran22
    6 years ago

    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

     

     

     

     

4 Replies

  • vivran22's avatar
    vivran22
    Community Champion
    6 years ago

    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's avatar
      Sky571
      Advocate I
      6 years ago

      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's avatar
        vivran22
        Community Champion
        6 years ago

        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