cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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
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
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
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
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

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.