help with formulating calculate filtered with variable dax

[Anonymous]

Hi,

I have a issue that I'm not able to resolve with dates. I have a dimcalendar and workagreement("Työsopimus") table with start and enddate. When I create a relationship, other of the date relations is inactive. I'm having a barchart with line visual and ttrying to show new hires, new hires same period last year and Number of employees YoY%. Visual is not showing correctly because of need to use these two dates.

I'm trying to overcome the problem with breaking the measures in smaller pieces in order to use both enddate and sstartdate relationship to dimCalendar table but I don't know how to resolve the following as the variables gives an error multiple columns cannot be converted to a scalar value.

NOE =

var wagreementStartDate = FILTER(DimCalendar,DimCalendar[Date] <= TODAY()))

var wagreementEndDate = CALCULATE(FILTER(DimCalendar,DimCalendar[Date] >=TODAY()), 

USERELATIONSHIP('Työsopimus'[enddate],DimCalendar[Date]))

RETURN

 CALCULATE( COUNTROWS('Työsopimus'), wagreementStartDate, wagreementEndDate)

[PC2790]

The code is incorrectly used.

Can you give a sample of your data to understand how it looks like and also the expected result?

[Anonymous]

@PC2790 Hi, the case is basically exactly the same as this sample https://docs.microsoft.com/fi-fi/power-bi/create-reports/sample-human-resources

Only difference from that example is that in my case the number of employees (NOE) needs both start and enddates for the calculation because I need to filter startdates that are >= today and the end dates <= than today or enddate is blank.

[PC2790]

NOE = 
CALCULATE(COUNTROWS(EmployeeTable),FILTER(EmployeeTable,(EmployeeTable[enddate] == BLANK() || EmployeeTable[enddate] >=TODAY()) && EmployeeTable[startdate] <= TODAY()))

Here you go.

This gives an outcoe of 10 as expected.

[Anonymous]

Hi, everything is ok otherwise but I have a date dimension table that I need to use. So the issue remains the same, how to use the date dimension table and get the visual draw correctly because there is two datecolumns in workagreement table that I need to use for these calculatios. 

[PC2790 · Accepted solution]

Please refer attached my sample file where all the required calculations are there.

You can alos refer the blog which might cater to your requirement 

[Anonymous]

Hi @PC2790 and thanks a lot 😊. I will mark this as a solution, it didn't solve my ned staight away, but got me trails 🙂

I managed my visual to show correctly 

Datatables:

Workagreements example

ID	StartDate	Enddate
111	1.1.2022
2222	2.2.2021	31.12.2022

Then in the model there is DimStartdates and DimEnddates calendar tables  w relationsips

visual has three measures New hires (hires this year), hires last year and X-axis line value NOE YoY% change. YoY% change is calculated from NOE, NOE last year and NOE var - difference between those. Then it is divided NOE  var, NOE lastyear 
NOE (number of employees) = 

CALCULATE(

         COUNTx('Työsopimus','Työsopimus'[id]),

         FILTER('Työsopimus','Työsopimus'[startdate] <= TODAY()),

        'Työsopimus'[enddate]>=TODAY() || 'Työsopimus'[enddate]= BLANK())

NOE_endoflastyear =

  var thisyear = YEAR(TODAY())

  var lastyear = YEAR(TODAY())-1

    RETURN

       CALCULATE(

           COUNTROWS('Työsopimus'),

           FILTER(DimStartDate,DimStartDate[Date] <= DATE(lastyear,12,31)),

           DimEndDates[Date] >= DATE(lastyear,12,31) || DimEndDates[Date] = BLANK(),'Työsopimus'[id],CROSSFILTER('Työsopimus'[startdate],DimCalendar[Date],none))

NOE_endoflastyear =

  var thisyear = YEAR(TODAY())

  var lastyear = YEAR(TODAY())-1

    RETURN

       CALCULATE(

           COUNTROWS('Työsopimus'),

           FILTER(DimStartDate,DimStartDate[Date] <= DATE(lastyear,12,31)),

           DimEndDates[Date] >= DATE(lastyear,12,31) || DimEndDates[Date] = BLANK(),'Työsopimus'[id],CROSSFILTER('Työsopimus'[startdate],DimCalendar[Date],none))

NOE YoY% = DIVIDE([NOE var],[NOE_endoflastyear])

new hires = 

var thisyear = YEAR(TODAY())

return

CALCULATE(

    COUNTROWS('Työsopimus'),

    FILTER(DimStartDate,DimStartDate[Date] >=DATE(thisyear,1,1)

          ))

New Hires SPLY =

var lastyear = YEAR(TODAY())-1

var thisyear = YEAR(TODAY())

return

CALCULATE(

    COUNTROWS('Työsopimus'),

    FILTER(DimStartDate,DimStartDate[Date]>=DATE(lastyear,1,1) && DimStartDate[Date] < DATE(thisyear,1,1)),TREATAS(VALUES('Työsopimus'[startdate]),DimStartDate[Date]

    ))

[Anonymous]

Hi @PC2790 
sample of workagreement table: https://docs.google.com/spreadsheets/d/1Fi-bRL0TalSatqG2Fs_coCtJbDwShDX4oiMKwDT7Mo8/edit?usp=sharing

Calculations: New hires (new hires this year) New hires same period last year and NOE YoY% 
- NOE
- NOE last year
- NOE var = [NOE] - [NOE last year]
from those measures I calculate Noe YoY%: Noe YoY% = [NOE last year] - [NOE var]

Expected results are:
New hires = 8
new hires same period last year = 3
NOE = 10
NOE last year = 4
NOE var = 6
NOE YoY% = based on calculations

[Anonymous]

[PC2790]

Does this fulfill your requrirement?

NewHiresLastYear = 
var lastyear = YEAR(TODAY())-1
var thisyear = YEAR(TODAY())
return 
CALCULATE(COUNT(EmployeeTable[startdate]), FILTER(EmployeeTable,EmployeeTable[startdate]>=DATE(lastyear,1,1) && EmployeeTable[startdate] < DATE(thisyear,1,1)))

NewHires = 
var thisyear = YEAR(TODAY())
return
CALCULATE(COUNT(EmployeeTable[startdate]), FILTER(EmployeeTable,EmployeeTable[startdate]>=DATE(thisyear,1,1)))

What are you trying to achieve as part of NOE?