Forum Discussion

immkhan's avatar
immkhan
Frequent Visitor
6 years ago
Solved

How to calculate Growth based on multiple dimension

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...
Need Help
  • v-alq-msft's avatar
    v-alq-msft
    6 years ago

    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.

7 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    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/

  • v-alq-msft's avatar
    v-alq-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    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's avatar
      immkhan
      Frequent Visitor
      6 years ago

      can you share that sample .pbix file

      • v-alq-msft's avatar
        v-alq-msft
        Icon for Community Support rankCommunity Support
        6 years ago

        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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
    v-alq-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    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's avatar
      immkhan
      Frequent Visitor
      6 years ago

      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