Referencing and modifying one measure multiple times with CALCULATE in new measure

[Anonymous]

Hi,

I am looking for a way to referencing a measure only once in a new measure but still being able to modify through CALCULATE.

The goal is to see how the % share of total changes week over week, and I need to do this for many measures, like this:

Change Lead share WoW = 

VAR __current = [Count of Leads]

VAR __current_Total = CALCULATE( [Count of Leads],ALL('Last Campaign'[Type]))

VAR __WoW = CALCULATE([Count of Leads],DATEADD('Calendar'[Date],-7,DAY))

VAR __WoW_Total =  CALCULATE( [Count of Leads],ALL('Last Campaign'[Type]),DATEADD('Calendar'[Date],-7,DAY))

VAR __Current_share = DIVIDE(__current,__current_Total)

VAR __WoW_share = DIVIDE(__WoW,__WoW_Total)

var __share_change =  __Current_share - __WoW_share

RETURN __share_change

What I an looking for is something like this. I understand that this doesn't work since passing [Count of Leads] into the variable__measureName will already evaluate the measure and return the value from the current filter context. So I'd want to understand how to make this work.

Change Lead share WoW dyn = 

VAR __measureName =  [Count of Leads]

VAR __current = __measureName

VAR __current_Total = CALCULATE( __measureName,ALL('Last Campaign'[Type]))

VAR __WoW = CALCULATE(__measureName,DATEADD('Calendar'[Date],-7,DAY))

VAR __WoW_Total =  CALCULATE( __measureName,ALL('Last Campaign'[Type]),DATEADD('Calendar'[Date],-7,DAY))

VAR __Current_share = DIVIDE(__current,__current_Total)

VAR __WoW_share = DIVIDE(__WoW,__WoW_Total)

var __share_change =  __Current_share - __WoW_share

RETURN __share_change

[amitchandak]

@Anonymous , this should work. If calendar table is marked as date table and use in visual, measure etc

Change Lead share WoW dyn =

VAR __measureName = [Count of Leads]
VAR __current = __measureName
VAR __current_Total = CALCULATE( __measureName,ALL('Last Campaign'[Type]))
// or CALCULATE( __measureName,ALL('Last Campaign'))

VAR __Current_share = DIVIDE(__current,__current_Total)
VAR __lastweekShare = CALCULATE(__Current_share,DATEADD('Calendar'[Date],-7,DAY))
VAR __share_change = DIVIDE(__Current_share,__lastweekShare)
var __share_change = __Current_share - __WoW_share

RETURN __share_change

Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5bd4

You can consider week rank

Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123
https://www.youtube.com/watch?v=pnAesWxYgJ8
Time Intelligence, Part of learn Power BI https://youtu.be/cN8AO3_vmlY?t=27510

[Anonymous]

Thank you for the response. I've played around with your suggestion but the measure still doesn't get affected by either the DATEADD or the ALL() filter. 

I've recreated the scenario in a version of AdventureWorks, hopefully the link works (I'm not able to attach the file here). Are you able to make this work?

I've tried both my own and your suggested one through the variable:

VAR __lastweek_share_alt = CALCULATE(__Current_share,DATEADD('Date'[Date],-7,DAY))

https://www.dropbox.com/s/dvg00tbekk2wo86/AdventureWorks%20Sales.pbix?dl=0