IF Help

[Anonymous]

HI there,

I have a formula that is currently working now:

ANNUALIZED MARKET SHARE GAIN OR LOSS = IF([SALES - % CHG] > 0 , [GEO SALES MARKET SHARE PT CHG]*([MARKET SALES]/100))

I need to add the TIME FRAME element as I have a TIME FRAME Slicer with the following four options:

Latest 04WKS

Latest 13WKS

Latest 26WKS

Latest 52WKS

I want to take my formula above and then add in a multiplier factor based on the selected TIME FRAME. 

For example if they select Latest 04WKS then I need to multiple formula by 13, if they select Latest 13WKS multiple by 4, if they select Latest 26WKS multiple by 2 and finally if they select Latest 56WKS multiple by 1.

Help?

[Tahreem24]

Try the below dax:

= Switch(Table[LatestColumn],"Latest 04WKS", Measure*13, "Latest 13WKS", Measure*4,"Latest 26WKS", Measure*2,"Latest 52WKS", Measure*1)

[Anonymous]

I was able to figure it out with:

ANNUALIZED MARKET SHARE GAIN/LOSS = IF(SELECTEDVALUE(tffacts[TIME FRAME]) = "LATEST 04 WKS", ([GEO SALES MARKET SHARE PT CHG]*([MARKET SALES - CUR]/100)*13), IF(SELECTEDVALUE(tffacts[TIME FRAME]) = "LATEST 13 WKS", ([GEO SALES MARKET SHARE PT CHG]*([MARKET SALES - CUR]/100)*4), IF(SELECTEDVALUE(tffacts[TIME FRAME]) = "LATEST 26 WKS", ([GEO SALES MARKET SHARE PT CHG]*([MARKET SALES - CUR]/100)*2),IF(SELECTEDVALUE(tffacts[TIME FRAME]) = "LATEST 52 WKS", ([GEO SALES MARKET SHARE PT CHG]*([MARKET SALES - CUR]/100)*1)))))

But now I am trying to take ANNUALIZED MARKET SHARE GAIN/LOSS result and divide it into another measure that needs the same TIME FRAME added to it.

Here is the DAX that is accepted without errors but my visual is telling me Mdxscript(Model) (222, 415) Calculation error with [MARKET SHARE GAIN/LOSS % OF SALES - CUR]. The alternate result to return on divide by zero cases must be a constant numeric value:

MARKET SHARE GAIN/LOSS % OF SALES - CUR = IF(SELECTEDVALUE(tffacts[TIME FRAME]) = "LATEST 04 WKS", DIVIDE([ANNUALIZED MARKET SHARE GAIN/LOSS],([SALES - CUR]*13), IF(SELECTEDVALUE(tffacts[TIME FRAME]) = "LATEST 13 WKS", DIVIDE([ANNUALIZED MARKET SHARE GAIN/LOSS],([SALES - CUR]*4),IF(SELECTEDVALUE(tffacts[TIME FRAME]) = "LATEST 26 WKS", DIVIDE([ANNUALIZED MARKET SHARE GAIN/LOSS],([SALES - CUR]*2),IF(SELECTEDVALUE(tffacts[TIME FRAME]) = "LATEST 52 WKS", DIVIDE([ANNUALIZED MARKET SHARE GAIN/LOSS],([SALES - CUR]*1 )))))))))

[Anonymous]

I dont know how the exact measure will look like but it would use SELECTEDVALUE(Timeframeslicervalue) 

But i believe the timeframeslicer uses 2 different filter, the first and last date. So then you can use: 
MAX(SELECTEDVALUE(Timeframeslicervalue)) and MIN(SELECTEDVALUE(Timeframeslicervalue)).

With those values you can do a DATEDIFF() function that calculates the different between the dates. And then, when everything is done you can use that value in an IF statement.

Im willing to write everything for you but then i would need a report with sample data 🙂