How to calculate Month to date growth %?

[Anmolgan]

I have been able to calculate MTD calculations, and it seems to be working as usual, I used below formula to calculate MTD:

Iso MTD =

IF (

HASONEVALUE ( 'GRS Dump'[Financial Year] )

&& HASONEVALUE ('GRS Dump'[Date - Copy]),

CALCULATE (

SUM ( 'GRS Dump'[Subtotal Sale in Rs.] ),

FILTER (

ALL ( 'GRS Dump' ),

'GRS Dump'[Financial Year] = VALUES ( 'GRS Dump'[Financial Year] )

&& 'GRS Dump'[Date - Copy] = VALUES ( 'GRS Dump'[Date - Copy] )

&& 'GRS Dump'[Date] <= MAX ( 'GRS Dump'[Date] )

)

),

BLANK ()

)

and belo is my output:

Now I need to find Growth % on each month, can anyone explain me how can i calculate that??

[v-alq-msft]

Hi, @Anmolgan 

Based on your description, you may refer to the following links.

https://community.powerbi.com/t5/Desktop/how-to-calculate-growth-rate/m-p/253888

https://community.powerbi.com/t5/Desktop/Growth-rate-Month-Over-Month/m-p/128565

Could you please show me your sample data? Do mask sensitive data before uploading.

Best Regards

Allan

[Anmolgan]

@v-alq-msft  Unfortunately I tried to create the DAX custom column to calculate growth % using the below DAX but it gives me error saying A table of multiple values was supplied where a single value was expected. 

DAX

Revenue LM =

LOOKUPVALUE (

'GRS Dump'[Subtotal Sale in Rs.],

'GRS Dump'[Category], 'GRS Dump'[Category],

'GRS Dump'[Year Number], IF ( 'GRS Dump'[Month Number] = 1, 'GRS Dump'[Year Number] - 1, 'GRS Dump'[Year Number] ),

'GRS Dump'[Month Number], IF ( 'GRS Dump'[Month Number] = 1, 12, 'GRS Dump'[Month Number] - 1 )

)

Will share the Pbix file in your private chat

[Anmolgan]

Hi @v-alq-msft did you get the access of the file? any updates on the growth%??

[v-alq-msft]

Hi, @Anmolgan 

Thanks providing your sample file. Howerer, I don not think it is the data for this case. Please check it.

Best Regards

Allan

[Anmolgan]

@v-alq-msft  can you tell me what makes you think the data is not realted for MTD growth % calculations?

[v-alq-msft]

Hi, @Anmolgan 

'Data','Monthly Collection Report PBI', 'Query1' Tables in the file are not consistent with the screenshot in the case. 

Best Regards

[Anmolgan]

@v-alq-msft  I have sent you the correct file, please check.

[v-alq-msft · Accepted solution]

Hi, @Anmolgan 

I am sorry for the late reply. Based on your problem, you may create a measure as follows.

MonthGrowth % = 
var _currentyear = SELECTEDVALUE('GRS Dump'[Financial Year])
var _currentmonth = SELECTEDVALUE('GRS Dump'[Month Number])

var _currentvalue = 
CALCULATE(
    SUM('GRS Dump'[Subtotal Sale in Rs.]),
    FILTER(
        ALL('GRS Dump'),
        'GRS Dump'[Financial Year] = _currentyear&&
        'GRS Dump'[Month Number] = _currentmonth
    )
)
var _previousvalue = 
CALCULATE(
    SUM('GRS Dump'[Subtotal Sale in Rs.]),
    FILTER(
        ALL('GRS Dump'),
        'GRS Dump'[Financial Year] = _currentyear&&
        'GRS Dump'[Month Number] = _currentmonth - 1
    )
)
return
    IF(
        _currentmonth = 4,
        0,
        DIVIDE(_currentvalue-_previousvalue,_previousvalue)
    )

Result:

Best Regards

Allan

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

[Anmolgan]

@v-alq-msft Thanks I will validate this and let you know, thanks for the reply.

[Anmolgan]

@v-alq-msft  let me check the pbix file, I might have sent you the wrong one.

[Anmolgan]

@v-alq-msft  Thanks for the reply i will try this out.

[amitchandak]

Can you time intelligence function? Please make sure you have date table for that.

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 (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-12,MONTH))))

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")))
Rolling 3 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-3,MONTH)) 
Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date Filer],MAX(Sales[Sales Date]),-12,MONTH))  
3 month back MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-3,MONTH)))
 


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

Last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,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)))



YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"3/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))
2 Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(sales,sales[date] <=maxx(date,date[date])))

Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-12,MONTH))  
Rolling last 12 before 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(dateadd('Date'[Date],-12,MONTH)),-12,MONTH))  

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/

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin

[Anmolgan]

@amitchandak Thanks for the reply, and yes I have a date table, but I had actually calculated the MTD information, I just need to calculate growth percentage of the MTD sales, how can I perform that?

I was not able to find the DAX function on the list of your time related DAX functions.