visual not showing timeline correctly dmicalendar problem

[Anonymous]

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)

[Anonymous · Accepted solution]

solution for this can be found here
https://community.powerbi.com/t5/Desktop/help-with-formulating-calculate-filtered-with-variable-dax/...

[v-yangliu-msft]

Hi  @Anonymous ,

You can try to use the calculation table to create a date table here, use the date of this date table as the X-axis and do the calculation..

Table =
CALENDAR(
    MIN('Dimstartdate'[Date]),MAX('Dimstartdate'[Date]))

Or change the relationship between tables

https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-create-and-manage-relationships

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

here is excel with tables used and measures involved to this visual.
https://docs.google.com/spreadsheets/d/1Fi-bRL0TalSatqG2Fs_coCtJbDwShDX4oiMKwDT7Mo8/edit?usp=sharing

relationships