Calculate Percentage change

[Anonymous]

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

[amitchandak]

@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.

[Anonymous]

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.

@amitchandak 

 Thanks, Prof