Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
Hi,
Share some data, describe the question and show the expected result.
@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...
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))
Thanks in advance
@Anonymous
why does your variables CalendarDateWeek and CalendarDateWeek are absolutely the same?
and CalendarDateMonth and CalendarDateYear the same as well?
@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
User | Count |
---|---|
139 | |
113 | |
103 | |
77 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |