semi-additive measure only working with month nr. filter and not with month name filter

[S3]

Hello, 

I have a calculation of a semi-additive measure and a table showing this measure in Columns Header, and all the product names in the Row Headers. 
When I have a slicer filter of month names and select anyyy month, the table becomes empty. However, when I have a slicer with the month's number, then the measure shows the values for each product in that month. 

The measure is:

A Measure = AVERAGEX(VALUES(Dates[Monthnr]),[B Measure])

The problem persists also when I use the Month Name column instead of the Monthnr

I've tried reseraching this but couldn't find any solution. 

Thank you. 

[Greg_Deckler]

@S3 Going to depend on what you have in your table. However, I wouldn't recommend using VALUES but rather DISTINCT. Probably not your problem but you can run into issues with VALUES if you have unmatched rows.

[S3]

Thank you Greg_Deckler for your tip, yes it didn't solve it but I implemented what you said for a better measure

[Greg_Deckler]

@S3 So, it sounds like maybe there is a relationship problem or something. Can you provide more information on your data model? Could you do something like:

Measure = 
  VAR __Table = SUMMARIZE('Table',[Month],"__Value",[B Measure])
RETURN
  AVERAGEX(__Table,[__Value])

Really need more information but this may help, watch the end of this video where I go through Average per Category. https://youtu.be/QVHb59vbLf0

[S3]

the formula didn't work in some graphs, and other graphs has given me the same results. I would really appreciate your reply on what I shared yesterday, because it's a problem I can't seem to understand its reason.. Thanks a lot!

[S3]

Thanks again. Here are sample tables. The Dates Table is day by day, I summarized here each month in one row, but each month has all days in the month. I also have only 2022 for a year, so "January" would mean anyway January 2022.

Thanks SO much. I will for sure also watch the video

Product		Date		Purchase		Cancellation
Red		01.01.2022		26		0
Red		31.01.2022		28		3
Red		01.02.2022		28		0
Red		28.02.2022		27		5
Red		01.03.2022		27		0
Red		15.03.2022		26		5
Black		01.01.2022		5		0
Black		31.01.2022		4		1
Black		01.02.2022		4		0
Black		28.02.2022		4		0
Black		01.03.2022		4		0
Black		15.03.2022		4		1
Green		01.01.2022		14		0
Green		31.01.2022		14		0
Green		01.02.2022		14		0
Green		28.02.2022		13		1
Green		01.03.2022		13		0
Green		15.03.2022		14		0

Date		Monthid		Month Name
01.01.2022		202201		January
01.02.2022		202202		February
01.03.2022		202203		March

[S3]

Here are my measures (the purchases column is the total from all previous days, so this is why I need only the first day of the month, the rest of the columns are day by day)

Purchases Measure = SUM('Original'[purchases])
-----------------

Purchases first Day in Month =

var StartOfMonthDate = STARTOFMONTH('Dates'[Date])

var Output =

CALCULATE (

[Purchases Measure],

KEEPFILTERS( 'Dates'[Date] = StartOfMonthDate )

)

return

Output

-------------

Purchases first day next month =

var a = ADDCOLUMNS(VALUES('Dates'[Monthid]),"p",

var d = [Monthid]

return
calculate (MIN('Dates'[Monthid]),'Dates'[Monthid]>d))

var b = ADDCOLUMNS(a,"v",var p=[p] return CALCULATE('Subscriptions Measures'[Active Subscriptions first Day in Month],'Dates'[Monthid]=p))

return

sumx(b,[v])

---------------

Churn Measure = DIVIDE(SUM('original'[cancellations]),'Table Measures'[purchases first day next month])

-----------------

SemiAverage Churn = AVERAGEX(DISTINCT(Dates[Monthid]),[Churn Measure])