Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Return statement using variables

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))
7 REPLIES 7
Ashish_Mathur
Super User
Super User

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

@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
Resolver IV
Resolver IV

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

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))

 

Screenshot 2021-04-01 051458.png

Thanks in advance

 

Anonymous
Not applicable

@jeroendekk thanks but this is giving me blank values.

az38
Community Champion
Community Champion

@Anonymous 

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

and CalendarDateMonth and CalendarDateYear the same as well?


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn
Anonymous
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.