Forum Discussion
Calculate Percentage change
Hi Profs,
I have a question related to this, in example 4_27, 5_25, 6_22 will not be fixed, meaning the date will change. Now there is problem that how to calculate the percentage with the dynamic date....
https://1drv.ms/x/s!Am9VKMj_rOZJoHX29cZH2kpjmqAD?e=zq76F4
the description is here
for the quantity I want to compare based on the date, and the date will change, not fixed, next month will auto add the new date with the relevant values, and the 4_27 will not exist. For this part, I have already done, just that for the percentage change, need to compare the Qty such that how many percentage increase or decrease using the date col for each category like BG, Divsion Name etc.
Thanks in advance Profs
2 Replies
- amitchandak
Super User
Anonymous , is it Month vs Month or Day Vs Day, You can use time intelligence in a given manner with time table
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"))) diff = [MTD Sales]-[last MTD Sales] diff % = divide([MTD Sales]-[last MTD Sales],[last MTD Sales])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/
See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184
https://community.powerbi.com/t5/Community-Blog/Decoding-Direct-Query-in-Power-BI-Part-1-Time-Intelligence-in/ba-p/922885
Appreciate your Kudos.Hi Prof,
Actually is to compare based on the text Date 4_27,5_25 and for each date has the same period of data, and needs to compare within each category such as BG, Division Name, Commodity, Country Code, Inventory Status Code.
Thanks, Prof
