Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have the following code to pull in sales data from the previous year for each sales rep in my organization:
Last Year SDS Rev QTD = var _firstDayOfQuarter = DATEVALUE(LOOKUPVALUE(FiscalCalendarLabels[Label_Value],FiscalCalendarLabels[Label_Desc],"Last Year Same Quarter Start")) var _today = DATEVALUE(LOOKUPVALUE(FiscalCalendarLabels[Label_Value],FiscalCalendarLabels[Label_Desc],"Current Date")) var _sameDayLastYear = date(year(_today)-1,month(_today),day(_today)) var _lastDayOfQuarter = DATEVALUE(LOOKUPVALUE(FiscalCalendarLabels[Label_Value],FiscalCalendarLabels[Label_Desc],"Last Year Same Quarter End")) var _endDateToUse = if(_sameDayLastYear > _lastDayOfQuarter, _lastDayOfQuarter, _sameDayLastYear) var _deployLY_QTD = CALCULATE( sum(DemandReport[SDS Revenue]), FILTER( ALL(FiscalCalendar), FiscalCalendar[ACTUAL_DATE] >= _firstDayOfQuarter && FiscalCalendar[ACTUAL_DATE] <= _endDateToUse ) ) return _deployLY_QTD
I have a table visual to confirm that it's working, and it is for about half of my sales reps. Blanks are showing up for the other half.
I have confirmed that the reps with blanks do have sales data from last year in the actual table, but for some reason it's not pulling in.
Any help here is greatly appreciated! I've been trying to figure this measure out for about 5 days now!
Solved! Go to Solution.
Thanks for your response. I found the issue was with my relationships between tables.
Thanks again!
Without seeing more, what if you made the following changes in your last variable?
Last Year SDS Rev QTD = VAR _firstDayOfQuarter = DATEVALUE ( LOOKUPVALUE ( FiscalCalendarLabels[Label_Value], FiscalCalendarLabels[Label_Desc], "Last Year Same Quarter Start" ) ) VAR _today = DATEVALUE ( LOOKUPVALUE ( FiscalCalendarLabels[Label_Value], FiscalCalendarLabels[Label_Desc], "Current Date" ) ) VAR _sameDayLastYear = DATE ( YEAR ( _today ) - 1, MONTH ( _today ), DAY ( _today ) ) VAR _lastDayOfQuarter = DATEVALUE ( LOOKUPVALUE ( FiscalCalendarLabels[Label_Value], FiscalCalendarLabels[Label_Desc], "Last Year Same Quarter End" ) ) VAR _endDateToUse = IF ( _sameDayLastYear > _lastDayOfQuarter, _lastDayOfQuarter, _sameDayLastYear ) VAR _deployLY_QTD = CALCULATE ( SUM ( DemandReport[SDS Revenue] ), FILTER ( ALL ( FiscalCalendar ), MAX ( FiscalCalendar[ACTUAL_DATE] ) >= _firstDayOfQuarter && MAX ( FiscalCalendar[ACTUAL_DATE] ) <= _endDateToUse ) ) RETURN _deployLY_QTD
Thanks for your response. I found the issue was with my relationships between tables.
Thanks again!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
98 | |
78 | |
65 | |
60 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |