Combination chart - % difference measure - drill down from Y-> M/W/D

[Anonymous]

Hi Everyone,
 
I am trying to build a combination chart with bar and line. Bar represents sales and the view drills down from Y -> M-> W-> Day. Line should represent percentage difference from previous value based on what view (Year/Month/Week/Day) it is on. So for % difference measure I believe I cant use any TimeIntelligence functions as it needs to change dynamically so it should be something like a Table calculation  (current value - prev value)/Prev value and it should get calculated for each row based on the view it is on (Y/M/W/D). How to achieve this in DAX? Please help! Thanks!

[v-danhe-msft]

Hi @Anonymous,

Could you please tell me if your problem has been solved? If it is, could you please mark the helpful replies as Answered to close this topic?

Regards,

Daniel He

[Anonymous · Accepted solution]

Since I had to dril down to Week and as well I had to take a differnet approach. I created separate measures for each and created different views and used bookmarks and buttons to switch between them.

[v-danhe-msft]

Hi @Anonymous,

Based on my test, you could refer to below step:

Sample data:

Create below formula:

Measure = var a=MONTH(PREVIOUSMONTH('Table1'[Date]))
var b=IF(CALCULATE(SUM(Table1[Sales]),FILTER(ALL('Table1'),MONTH('Table1'[Date])=a))=BLANK(),BLANK(),CALCULATE(SUM(Table1[Sales]),FILTER(ALL('Table1'),MONTH('Table1'[Date])=a)))
return (CALCULATE(SUM(Table1[Sales]))-b)/b

Result:

You could also download the pbix file to have a view.

Regards,

Daniel He