Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

visual not showing timeline correctly dmicalendar problem

Hi,

 

I have multiple tables wich have two dates fields startdate and enddate. This is why I have created dimstartdates and dimenddates calendars.

Now I have try to create a visual, where there is new hires, new hires same period last year and Number of employees % change.

Measure for new hires is 

New Hires =
 var _date = TODAY()
 var _endofyear = DATE(YEAR(_date),31,12)
 var _startofyear = DATE(YEAR(_date),1,1)
 RETURN
  CALCULATE(  COUNTROWS('Työsopimus'), FILTER( DimStartDate, DimStartDate[Date] <= _endofyear && DimStartDate[Date] >= _startofyear ))

New hires previous year is
New Hires SPLY =
  Var _date = date(year(today())-1, month(today()) , day(TODAY())) //set today to variable
  var endofyeara = DATE(YEAR(_date),12,31) //changes the same period last year end date to end of year
  var periodenddate = MAX(DimStartDate[Date])
  var periodstartdate = MIN(DimEndDates[Date])
RETURN
    CALCULATE(COUNTROWS('Työsopimus'), FILTER( 'DimStartDate', DimStartDate[Date] <= endofyeara),  FILTER(DimStartDate,DimStartDate[Date] <= periodstartdate),  'DimEndDates'[Date] >= periodenddate || DimEndDates[Date]= BLANK()
    )

Then the third measure is Number Of Employees end of last year
NOE_endoflastyear =
Var _date = today()
 var periodenddate = MAX(DimEndDates[Date])
 var periodstartdate = MIN(DimEndDates[Date])

 RETURN
    CALCULATE( COUNTROWS( 'Työsopimus'), FILTER(DimStartDate,DimStartDate[Date] <= periodstartdate), 'DimEndDates'[Date] >= periodenddate || DimEndDates[Date]= BLANK()
   )
if I put the visual time line Month from Dimstartdate table column calues are correct, but noe% is incoorect


then if I change the month to come from dimenddates table the line is correct but columns not and I can't figure out how to get over this issue (I understand that the problem is that NOE endoflastyear uses dimenddate from where the noe YoY% is calculated and other measures use dimstartdate)

 



Need Help

3 Replies