Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have been able to calculate MTD calculations, and it seems to be working as usual, I used below formula to calculate MTD:
Solved! Go to 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.
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
@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
Hi @v-alq-msft did you get the access of the file? any updates on the growth%??
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
@v-alq-msft can you tell me what makes you think the data is not realted for MTD growth % calculations?
Hi, @Anmolgan
'Data','Monthly Collection Report PBI', 'Query1' Tables in the file are not consistent with the screenshot in the case.
Best Regards
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.
@v-alq-msft Thanks I will validate this and let you know, thanks for the reply.
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
@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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
106 | |
105 | |
86 | |
72 |