Forum Discussion
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.
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.
1 Reply
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.