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

Question

Hello everyone,&nbsp;I have a table in my report where I use two measures that are as follows:&nbsp;measure c =&nbsp;DIVIDE((SUM('Table[Value A])*12),SUM('Table'[Value B]))&nbsp;and measure to calculate the average of this measure c&nbsp;average measure c =&nbsp;AVERAGEX ( VALUES ( Calendar[Date].[Month] ), [measure c] )&nbsp;This is the result of my table:dateteamvalue Avalue Bmeasure Caverage measure Cago/21A3711,45166193908,10,02680%0,02680%set/21A2549,991700199410,01800%0,01800%out/21A19605,06173440583,50,13564%0,13564%nov/21A24001,52180851849,50,15926%0,15926%dez/21A14640,11189116781,80,09290%0,09290%jan/22A5830,26197560129,60,03541%0,03541%fev/22A16711,02206557220,30,09708%0,09708%mar/22A16551,37203876476,50,09742%0,09742%abr/22A14733,25201803932,30,08761%0,08761%mai/22A17461,04207193959,20,10113%0,10113%jun/22A31595,37218213661,20,17375%0,17375%jul/22A76286,54224224552,40,40827%0,40827%TOTAL&nbsp;243676,9823390529950,12501%0,11944%&nbsp;The result of my average of measure c in this table, using&nbsp;Calendar[Date].[Month]&nbsp;is 0,11944%. The problem is, when I change to&nbsp;Calendar[Date].[Year]&nbsp;my result is 0,11766%.&nbsp;Could someone please explain to me why I'm having this result change and what would be the correct one?&nbsp;Thank you

greg_deckler · Answer

hama21&nbsp;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/63376Also, 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

greg_deckler · Answer

hama21&nbsp;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:&nbsp;https://youtu.be/vOUdKXZpmSc
&nbsp;

hama21 · Answer

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

greg_deckler · Answer

hama21&nbsp;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 · Answer

Greg_Deckler&nbsp;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

Forum Discussion

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

5 Replies

Related content

Extract month and year from date

Transform a date to year-month

Calcuate AHT by date, month, quarter and year.

Date Table - Years and Months Only

Difference in sales from previous year end date to current year month end dates

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%