Return statement using variables

[Anonymous]

I'm trying to use this variable statement for a column in a table when the date slicer is used but I know something isn't correct with my RETURN statement.

I get the correct value if I leave off the second IF statement. I can get all correct totals when I change out my variables, but I can't seem to get all 4 variables to work in my RETURN Statement. As it's written right now, I get my Day and Week, but the Month and Year are off just a tad, but if I create a measure with the SAMEPERIODLASTYEAR and use it seperately the totals are correct. Any thoughts?

var CalendarDateMonth = CALCULATE(SUM('GCE$Revel POS Daily Sales'[Actual]),SAMEPERIODLASTYEAR('Date Table'[Date]))

var CalendarDateYear = CALCULATE(SUM('GCE$Revel POS Daily Sales'[Actual]), SAMEPERIODLASTYEAR('Date Table'[Date]))

var CalendarDateWeek = CALCULATE(SUM('GCE$Revel POS Daily Sales'[Actual]),DATEADD('Date Table'[Date], -364,DAY))

var CalendarDateDay = CALCULATE(SUM('GCE$Revel POS Daily Sales'[Actual]),DATEADD('Date Table'[Date], -364,DAY))

RETURN

IF(SELECTEDVALUE('Date Table'[Date],CalendarDateDay),CalendarDateWeek,

IF(SELECTEDVALUE('Date Table'[Date],CalendarDateMonth),CalendarDateYear))

[Ashish_Mathur]

Hi,

Share some data, describe the question and show the expected result.

[Anonymous]

@Ashish_Mathur I have a whole thread on what I'm trying to accomplish with some data details. https://community.powerbi.com/t5/Desktop/Prior-Year-Sales-using-454-Calendar-with-Date-Slicer/td-p/1... 

[jeroendekk]

Hi Dawn,

The first argument of the IF statement should return TRUE or FALSE. So it should contain an Operator (>, <, <>, = etc) In your RETURN

RETURN
IF(SELECTEDVALUE('Date Table'[Date],CalendarDateDay),CalendarDateWeek,
IF(SELECTEDVALUE('Date Table'[Date],CalendarDateMonth),CalendarDateYear))

The first argument returns either the selected date of the value of the variable.

Maybe you mean 

RETURN
IF(SELECTEDVALUE('Date Table'[Date]) = CalendarDateDay,CalendarDateWeek,
IF(SELECTEDVALUE('Date Table'[Date) = CalendarDateMonth,CalendarDateYear))

 Hopes this helps

Best regards

[brinky]

Hello,

I found your return suggestion very useful. I am placing this measure in a matrix, is there a way I could also get the Total showing?

Total Ext Val = 
VAR tp = CALCULATE(
    SUM('04 Fact'[turnover]),
    FILTER(OcareDB,OcareDB[CategoryID_gps]= "Toothpaste"))/.33 

VAR dn = CALCULATE(
    SUM('04 Fact'[turnover]),
    FILTER(OcareDB,OcareDB[CategoryID_gps]= "Denture"))/.20 

RETURN
IF(SELECTEDVALUE(OcareDB[CategoryID_gps]) = "Toothpaste", tp,
IF(SELECTEDVALUE(OcareDB[CategoryID_gps]) = "Denture", dn))

Thanks in advance

[Anonymous]

@jeroendekk thanks but this is giving me blank values.

[az38]

@Anonymous 

why does your variables CalendarDateWeek  and CalendarDateWeek  are absolutely the same?

and CalendarDateMonth and CalendarDateYear the same as well?

[Anonymous]

@az38 I'm new at this and I guess I wrote it out this way so I could logically keep up with what I was trying to achieve. However, in that same note, I tried combining it using 

var CalendarDateDayWeek = 

CALCULATE(SUM('GCE$Revel POS Daily Sales'[Actual]),DATEADD('Date Table'[Date], -364,DAY))

as well as the combinging the monthyear as above, but all my totals are off. Oddly enough I'm getting the correct values the way I have it written seperately out as opposed to try to combine the variable. I thought that was odd.