Difference between date.[month] and date.[year]

[hama21]

Hello everyone,

I have a table in my report where I use two measures that are as follows:

measure c = DIVIDE((SUM('Table[Value A])*12),SUM('Table'[Value B]))

and measure to calculate the average of this measure c

average measure c = AVERAGEX ( VALUES ( Calendar[Date].[Month] ), [measure c] )

This is the result of my table:

date	team	value A	value B	measure C	average measure C
ago/21	A	3711,45	166193908,1	0,02680%	0,02680%
set/21	A	2549,99	170019941	0,01800%	0,01800%
out/21	A	19605,06	173440583,5	0,13564%	0,13564%
nov/21	A	24001,52	180851849,5	0,15926%	0,15926%
dez/21	A	14640,11	189116781,8	0,09290%	0,09290%
jan/22	A	5830,26	197560129,6	0,03541%	0,03541%
fev/22	A	16711,02	206557220,3	0,09708%	0,09708%
mar/22	A	16551,37	203876476,5	0,09742%	0,09742%
abr/22	A	14733,25	201803932,3	0,08761%	0,08761%
mai/22	A	17461,04	207193959,2	0,10113%	0,10113%
jun/22	A	31595,37	218213661,2	0,17375%	0,17375%
jul/22	A	76286,54	224224552,4	0,40827%	0,40827%
TOTAL		243676,98	2339052995	0,12501%	0,11944%

The result of my average of measure c in this table, using Calendar[Date].[Month] is 0,11944%. The problem is, when I change to Calendar[Date].[Year] my result is 0,11766%. Could someone please explain to me why I'm having this result change and what would be the correct one?

Thank you

[Greg_Deckler]

@hama21 Well, the reason is that you are taking an average across either 2 rows (Year) or 12 rows (Month). It is also possible that VALUES is returning a blank row and that might also throw some things off. I think that your formula would be much more clear and more flexible if you did not use the auto-time intelligence stuff which has the potential to cause issues as well as use SUMMARIZE or DISTINCT versus VALUES. Seriously, don't use VALUES: https://youtu.be/vOUdKXZpmSc

[hama21]

How do you recommend my formula? I'm watching the video right now. Thanks for the answer @Greg_Deckler 

[Greg_Deckler]

@hama21 I would need a better sense of your source data to be 100% sure. Is what you posted a good example of how your source data looks? But, I would personally try to do something along the lines of:

Average Measure =
  VAR __Table =
    SUMMARIZE(
      ADDCOLUMNS(
        'Table',
        "__Month",MONTH('Table'[Date])
      ),
      [__Month],
      "__Measure",[measure c]
    )
RETURN
  AVERAGEX(__Table,[__Measure])

A little more verbose because I don't know if you have a Month column already. The advantages of this approach is that you can troubleshoot it by using CONCATENATEX instead of AVERAGEX to inspect your table values within the calculation itself. Also, this method removes any issues that might be caused via table configuration. This form works in a single table data model as well as a star, schema, etc.

[hama21]

@Greg_Deckler It's working but I received different value when using .[month] or .[year]. I want to understand why this is happening because the value must be the same

[Greg_Deckler]

@hama21 Ah, turns out that it is a measures total problem. I attached a PBIX that demonstrates how to get the same total for both .[Month] and .[Year] below signature.

This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907