calculate semi-additif measure for each month DAX

[YassineERRASFY]

hello,

I'm working on a SSAS Tabular model, I have two tables : 360ST and PERIODE, they are linked by date[MMDDYYYY] granularity columns.

I have a semi-additif measure from the table 360ST that is at week granularity level, I want to calculate that measure for each month : the value of that measure for each month is the value for the last week in that particular month.

I have created the measure as follows :

Carnet DDP:=

CALCULATE (

SUM ('360ST'[NBCMD] ),

TREATAS (

VALUES ( PERIODE[Annee Semaine]),

'360ST'[CMD_YEARWEEK]
),
CROSSFILTER ( '360ST'[DAT CRE ENT], PERIODE[DateEntier],None),
CROSSFILTER ( '360ST'[DAT MES 360ST ENT], PERIODE[DateEntier],None),
DATESBETWEEN(
PERIODE[Date],
DATEADD( ENDOFMONTH( PERIODE[Date]), -7, DAY ),
ENDOFMONTH( PERIODE[Date])
)
)

I have used datebetween, dateadd and endofmonth to get the last week in each month, but It does not work.

what I have :

MONTH	WEEK	MEASURE
Sep 2020	202039	109943
Sep 2020	202040	108669
Oct 2020	202040	109336
Oct 2020	202041	108115
Oct 2020	202042	11335
Oct 2020	202043	110103
Oct 2020	202044	110980

what I want :

MONTH	MEASURE
Sep 2020	108669
Oct 2020	110980

Thanks in advance.

[wdx223_Daniel]

@YassineERRASFY  assume the MONTH and WEEK come from a standard date table.

=VAR vMaxWeek=CALCULATE(MAX(DateTable[WEEK]),DATESMTD(ENDOFMONTH(DateTable[Date]))) VAR vCurrentWeek=MAX(DateTable[WEEK]) RETURN IF(vCurrentWeek=vMaxWeek,[MEASURE])

[Anonymous]

By the way... Would you please show the model? I'm not convinced that the model is correct. Also, please show the measures. Thanks.

[Anonymous]

Standard weeks do not go evenly into months. When you say "the value for the last week in that particular month", what do you exactly mean? Which week is considered the "last" in a particular month? The last full week in that month? Or, for instance, the very last week in the month regardless of whether it goes fully into the month or not?