Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
@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])
By the way... Would you please show the model? I'm not convinced that the model is correct. Also, please show the measures. Thanks.
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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
54 | |
27 | |
23 | |
14 | |
11 |
User | Count |
---|---|
77 | |
63 | |
44 | |
17 | |
12 |