Forum Discussion
help with formulating calculate filtered with variable dax
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 =
9 Replies
PC2790Community Champion
The code is incorrectly used.
Can you give a sample of your data to understand how it looks like and also the expected result?
Hi PC2790
sample of workagreement table: https://docs.google.com/spreadsheets/d/1Fi-bRL0TalSatqG2Fs_coCtJbDwShDX4oiMKwDT7Mo8/edit?usp=sharingCalculations: 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 calculationsPC2790 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.