Count and sum for specific column then compare between years

[NickProp28]

Dear experts,

Good day. I new to Power BI, and would like get some help from you guys.

In short,here is my data.

Date                Month    Country   Department

9/6/2019	June	Chennai	IT
9/6/2019	June	Chennai	IT
9/6/2019	June	USA	IT
9/6/2020	June	USA	HR
10/6/2020	June	USA	HR
10/7/2019	July	Chennai	IT
11/7/2019	July	Chennai	HR
11/7/2020	July	USA	HR
11/7/2020	July	USA	IT

And here my expected outcome .Count and sum the department, and i would like to compare between two years. (Will add country in my slicer, but if not the outcome will like this)

	2019	2020
June	3	2
July	2	2

My sincerely appreciate for the help.

[v-lionel-msft · Accepted solution]

Hi @NickProp28 ,

Please refer to my .pbix file.

Best regards,
Lionel Chen

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

[v-lionel-msft · Accepted solution]

Hi @NickProp28 ,

I think it would be very strange for you to add measure 2 to the same matrix, because the column value of your matrix are years.

So I created another table visual.

Are you sure what you want is the following visual effect?

Best regards,
Lionel Chen

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

[v-lionel-msft · Accepted solution]

Hi @NickProp28 ,

Please refer to my .pbix file.

Best regards,
Lionel Chen

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

[NickProp28]

Dear @v-lionel-msft ,

Based on this question, can you help me out on divide this two years (Year 2020/year 2019)? I want get the Variance.
Thanks so much if you could help. 

[v-lionel-msft]

Hi @NickProp28 ,

You can do like this.

Measure 2 = 
VAR x = 
CALCULATE(
    COUNT(Sheet4[Department]),
    FILTER(
        Sheet4,
        Sheet4[Month] = MAX(Sheet4[Month] ) && Sheet4[Year] = 2019
    )
)
VAR y = 
CALCULATE(
    COUNT(Sheet4[Department]),
    FILTER(
        Sheet4,
        Sheet4[Month] = MAX(Sheet4[Month] ) && Sheet4[Year] = 2020
    )
)
RETURN
DIVIDE(
    y, x, BLANK()
)

Best regards,
Lionel Chen

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

[NickProp28]

Dear @v-lionel-msft ,

This is the solution I looking for, but i tried the code and seems does not work well for me. Would you mind share the .pbix to me?
I very much appreciate your support.

[v-lionel-msft · Accepted solution]

Hi @NickProp28 ,

I think it would be very strange for you to add measure 2 to the same matrix, because the column value of your matrix are years.

So I created another table visual.

Are you sure what you want is the following visual effect?

Best regards,
Lionel Chen

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

[NickProp28]

Dear @v-lionel-msft ,

I get what you mean. Yup, I get the outcome.
I'm sorry for asking, isit there have any possible way to make all into one table without separate it into two?

Year2019	Year2020	Variance

Best regards and thanks.

[v-lionel-msft · Accepted solution]

Hi @NickProp28 ,

Please use the formula.

Measure 2 = 
VAR x = 
CALCULATE(
    COUNT(Sheet4[Department]),
    FILTER(
       ALLSELECTED( Sheet4),
        Sheet4[Month] = MAX(Sheet4[Month] ) && Sheet4[Year] = 2019
    )
)
VAR y = 
CALCULATE(
    COUNT(Sheet4[Department]),
    FILTER(
        ALLSELECTED(Sheet4),
        Sheet4[Month] = MAX(Sheet4[Month] ) && Sheet4[Year] = 2020
    )
)
RETURN
DIVIDE(
    y, x, BLANK()
)

 

Best regards,
Lionel Chen

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