Forum Discussion

noobtopowerbi's avatar
noobtopowerbi
Frequent Visitor
2 years ago

compare with count previous year

I have a table1 containing customerid, month, presenceboolean. I have a calendartable containing date,month,year. Now I want to visualize a table with columns year, distinctcount customerid and dist...
Need Help
noobtopowerbi's avatar
noobtopowerbi
Frequent Visitor
2 years ago

I must be doing something wrong, I get the right results for previous year but not for the actual year.
As I cannot add a pbix file to my posts I will give as much informations as possible :

This is the measure for current year :

presence this year = calculate(DISTINCTCOUNT('Table'[employee]),
        Filter(all('Table'),'Table'[month]= 9),
        'Table'[presence]=1
)
For previous year: 
presence previous year =
 VAR Previous_Year = max('Table'[year])-1
 Return
 calculate(DISTINCTCOUNT('Table'[employee]),

        Filter(all('Table'),'Table'[month]= 9),'Table'[year]=Previous_Year,
        Filter(all('Table'),'Table'[presence]=1))

Now I get for the current year a count of all employees while not every value for presence =1 in month 9

 

 

 
 
  • ExcelMonke's avatar
    ExcelMonke
    Impactful Individual
    2 years ago

    Hi there, you may have a parentheses misplaced 🙂 
    Try:

    presence this year = calculate(DISTINCTCOUNT('Table'[employee]),
        Filter(all('Table'),'Table'[month]= 9,
        'Table'[presence]=1)
)