How to calculate Growth based on multiple dimension

[immkhan]

Hi

I want to calculate Growth for Current year (Month over Month), Growth over last year and Five Year Growth. The dimension to calculate growth is multiple.

1) Company , Country, Customer, Product, Year, Quarter, Month etc.

Any simple way to do it...

[v-alq-msft]

Hi, @immkhan 

If you take the answer of someone, please mark it as the solution to help the other members who have same problems find it more quickly. If not, let me know and I'll try to help you further. Thanks.

Best Regards

Allan

[immkhan]

Actually it is not giving the desired result which I want and therefore I have not marked as a complete solution.

Anyways you marked it as a solution and closes the thread.

Regards

[Anonymous]

To create a quick measure in Power BI Desktop, right-click or select the ellipsis ... next to any item in the Fields pane, and select New quick measure from the menu that appears.

Select New quick measure

You can also right-click or select the drop-down arrow next to any value in the Values well for an existing visual, and select New quick measure from the menu.

When you select New quick measure, the Quick measures window appears, letting you select the calculation you want and the fields to run the calculation against.

Select the Select a calculation field to see a long list of available quick measures.

[v-alq-msft · Accepted solution]

Hi, @immkhan 

Based on your description, I created data to reproduce your scenario.

Test:

Calendar:

Calendar = CALENDARAUTO()

There is a one-to-one relationship between two 'Calendar' and 'Table'.

You may create a measure as below.

PeriodValue = 
var _period = SELECTEDVALUE(Test[Period])
return
IF(
    _period = "Current year",
    CALCULATE(
        SUM('Table'[Value]),
        DATESYTD('Calendar'[Date])
    ),
    IF(
        _period = "Last year",
        CALCULATE(
            SUM('Table'[Value]),
            DATESYTD(SAMEPERIODLASTYEAR('Calendar'[Date]))
        ),
        IF(
            _period = "Last five years",
            CALCULATE(
                SUM('Table'[Value]),
                DATESINPERIOD(
                    'Calendar'[Date],
                    LASTDATE('Calendar'[Date]),
                    -5,
                    YEAR
                )
            )
        )
    )
)

You may also use other dimensions as slicers to filter the result. Here are the results.

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[immkhan]

can you share that sample .pbix file

[v-alq-msft]

Hi, @immkhan 

Here is the pbix .

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@immkhan 

Use time intelligence with date calendar

example

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
last year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH)))
last year MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-12,MONTH))))
Month behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Month))
last QTR same Month (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,Qtr))))
MTD (Year End) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFYEAR('Date'[Date])))
MTD (Last Year End) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFYEAR(dateadd('Date'[Date],-12,MONTH),"8/31")))

 

QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(('Date'[Date])))

Last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,QUARTER)))
Last complete QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD( ENDOFQUARTER(dateadd('Date'[Date],-1,QUARTER))))

Last to last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-2,QUARTER)))
Next QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],1,QUARTER)))

Last year same QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,Year)))
Last year same QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(ENDOFQUARTER(dateadd('Date'[Date],-1,Year))))

trailing QTR = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,QUARTER))
trailing  4 QTR = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-4,QUARTER))



YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"12/31"))
This Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD((ENDOFYEAR('Date'[Date])),"12/31"))

Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
Last YTD complete Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))

Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/