cancel
Showing results for
Search instead for
Did you mean:
Regular Visitor

## calculate semi-additif measure for each month DAX

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.

3 REPLIES 3
Super User II

@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])

Solution Sage

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

Solution Sage

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?

## Helpful resources

Announcements

#### Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

#### Check it Out!

Click here to read more about the March 2021 Updates!

#### The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

#### Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors