Forum Discussion

SofiaK's avatar
SofiaK
Regular Visitor
1 year ago
Solved

Create a visual comparing percentage difference in three months period

Hi, I need to create a visual showing percentage difference of number of companies in different industries in three months period e.i. june/july/august. 

 

But it only shows me two months not three.

 

 

I'm usuing this formula.

 

Percentage Difference =
VAR CurrentMonthCompanies = [Total Companies]
VAR PreviousMonthCompanies =
    CALCULATE(
        [Total Companies],
        DATEADD('Sheet1 (2)'[Date], -1, MONTH)
    )
RETURN
IF(
    NOT(ISBLANK(PreviousMonthCompanies)),  
    DIVIDE(CurrentMonthCompanies - PreviousMonthCompanies, PreviousMonthCompanies, 0) * 100,
    BLANK()
)
Need Help

1 Reply

  • rajendraongole1's avatar
    rajendraongole1
    Super User
    1 year ago

    Hi SofiaK - Check, your data source contains records for all three months, especially for August. If any month is missing, Power BI might only calculate the difference for the two available months.

    slightly you can modify your DAX formula to calculate the three-month trend by specifying the range directly or by creating measures for each of the three months. 

    Percentage Difference =
    VAR CurrentMonthCompanies = [Total Companies]
    VAR PreviousMonthCompanies =
    CALCULATE(
    [Total Companies],
    DATEADD('Sheet1 (2)'[Date], -1, MONTH)
    )
    VAR PreviousTwoMonthsCompanies =
    CALCULATE(
    [Total Companies],
    DATEADD('Sheet1 (2)'[Date], -2, MONTH)
    )
    RETURN
    IF(
    NOT(ISBLANK(PreviousMonthCompanies)) && NOT(ISBLANK(PreviousTwoMonthsCompanies)),
    DIVIDE(CurrentMonthCompanies - PreviousMonthCompanies - PreviousTwoMonthsCompanies, PreviousMonthCompanies + PreviousTwoMonthsCompanies, 0) * 100,
    BLANK()
    )

     

    dont forget to to have a Date dimension table, consider creating one. This allows for more accurate date-based calculations and lets you set up a continuous timeline across months.

     

    Hope this works.