cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Frequent Visitor

## Compute Sum of the month knowing week of the year

Hi
I have a very simple scenario on which I do not found a solution :
I have a common Calendar table

Calendar:
Calendar[date]
Calendar[YearWeekNumber]
Calendar[YearMonthNumber]

and some orders withing the following table

Commande:
Commande[date]
Commande[YearWeekNumber]
Commande[YearMonthNumber]
Commande[Price]

The user with the filter can select the Calendar[YearWeekNumber]
to compute the Sum of Commande[Price] for the selected week. I wanted to compute on the same screen the sum of the orders for the entire month too. I made the measure below :

MonthOrders = CALCULATE(Sum(Commande[Price]),DATESMTD(ENDOFMONTH('Calendar'[Date])))

The value of MonthOrders is fine if all days of the week selected are in the same month.
But the result is wrong if the days of the selected week are split between a month and the next one.

Regards.

4 ACCEPTED SOLUTIONS
Solution Sage
[Month Order Total] =
// This must work for any selection
// of weeks, not only one.
SUMX(
// Go over all the weeks
// in the current context. Please note
// that YearWeekNumber must be unique
// across all years. If this number
// does not uniquely identify the week
// in the whole Calendar, please use
// a field that does instead.
DISTINCT( 'Calendar'[YearWeekNumber] ),
// If the currently iterated-over
// week is not fully contained in
// just one month, then get the
// first month that has a non-empty
// intersection with the week.
CALCULATE(
// UniqueMonthID must be the unique identifier
// of the month across all the years and it
// must be increasing, so, for instance,
// Jan 2019 must have a different id from
// Jan 2020.
var __firstMonth =
MIN( 'Calendar'[UniqueMonthID] )
var __total =
CALCULATE(
SUM( 'Commande'[Price] ),
'Calendar'[UniqueMonthID] = __firstMonth,
ALL( 'Calendar' )
)
return
__total,

ALLEXCEPT(
'Calendar',
'Calendar'[YearWeekNumber]
)
)
)
Frequent Visitor

Your solution looks great, I did not know such kind of syntax with the definition of variables "on the fly" during the CALCULATE  function. I implemented it. The result is correct when I filter the page with a week num (or severals like you said) coming from the Calendar table but force me to present a long list of all weeks of the calendar to the user.
If I try to filter by the week num coming from the Commande then the result is always blank. Is there a good reason for this?
Regards

Solution Sage

You should NEVER use a field that comes directly from a fact table. Fact tables should be hidden, or, if you have measures in them, all their columns must be hidden. Slicing must only be possible through dimensions. ALWAYS. Do it differently and you'll be doomed. If you want to present only weeks that exist in your fact table, there are much better ways to do it. A slicer's entries can be filtered as well by any measure you want. Just use the Filter Pane.

One last thing... your fact table should only contain keys to dimensions and numbers (raw measures). Nothing else. Do it in a different way and you'll feel the heat in no time.

Community Support

Hi @FbBrnr

You may try may way as well, I build this measure by if function.

Measure =
VAR _sel =
SELECTEDVALUE ( 'Calendar'[YearWeekNumber] )
VAR _count =
CALCULATE (
DISTINCTCOUNT ( Commande[YearMonthNumber] ),
FILTER ( Commande, Commande[YearWeekNumber] = _sel )
)
VAR _MinxMon =
MINX (
FILTER ( Commande, Commande[YearWeekNumber] = _sel ),
Commande[YearMonthNumber]
)
VAR _A =
SUMX ( FILTER ( Commande, Commande[YearWeekNumber] = _sel ), Commande[Price] )
VAR _B =
SUMX (
FILTER (
Commande,
Commande[YearWeekNumber] = _sel
&& Commande[YearMonthNumber] = _MinxMon
),
Commande[Price]
)
RETURN
IF ( _count = 1, _A, _B )

_Count is the discount of rows when we filter by our slicer, If COUNT=1, show _A(the normal answer),if COUNT =2 , show you_B(The sum of min month price).

And if you want to use this measure, you may build a slicer by YearWeekNumber Column in Calendar table.(Due to we use Commande[YearWeekNumber] = _sel in our measure)

You can download the pbix file from this link: Compute Sum of the month knowing week of the year

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

7 REPLIES 7
Community Support

Hi @FbBrnr

You may try may way as well, I build this measure by if function.

Measure =
VAR _sel =
SELECTEDVALUE ( 'Calendar'[YearWeekNumber] )
VAR _count =
CALCULATE (
DISTINCTCOUNT ( Commande[YearMonthNumber] ),
FILTER ( Commande, Commande[YearWeekNumber] = _sel )
)
VAR _MinxMon =
MINX (
FILTER ( Commande, Commande[YearWeekNumber] = _sel ),
Commande[YearMonthNumber]
)
VAR _A =
SUMX ( FILTER ( Commande, Commande[YearWeekNumber] = _sel ), Commande[Price] )
VAR _B =
SUMX (
FILTER (
Commande,
Commande[YearWeekNumber] = _sel
&& Commande[YearMonthNumber] = _MinxMon
),
Commande[Price]
)
RETURN
IF ( _count = 1, _A, _B )

_Count is the discount of rows when we filter by our slicer, If COUNT=1, show _A(the normal answer),if COUNT =2 , show you_B(The sum of min month price).

And if you want to use this measure, you may build a slicer by YearWeekNumber Column in Calendar table.(Due to we use Commande[YearWeekNumber] = _sel in our measure)

You can download the pbix file from this link: Compute Sum of the month knowing week of the year

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Community Champion

@FbBrnr If the days of the week are split between 2 month then what do you want the formula to return? (which month's values?)

Frequent Visitor

For example, if the week is split between March and April I need to total amount of the orders of March.
Regards.

Solution Sage
[Month Order Total] =
// This must work for any selection
// of weeks, not only one.
SUMX(
// Go over all the weeks
// in the current context. Please note
// that YearWeekNumber must be unique
// across all years. If this number
// does not uniquely identify the week
// in the whole Calendar, please use
// a field that does instead.
DISTINCT( 'Calendar'[YearWeekNumber] ),
// If the currently iterated-over
// week is not fully contained in
// just one month, then get the
// first month that has a non-empty
// intersection with the week.
CALCULATE(
// UniqueMonthID must be the unique identifier
// of the month across all the years and it
// must be increasing, so, for instance,
// Jan 2019 must have a different id from
// Jan 2020.
var __firstMonth =
MIN( 'Calendar'[UniqueMonthID] )
var __total =
CALCULATE(
SUM( 'Commande'[Price] ),
'Calendar'[UniqueMonthID] = __firstMonth,
ALL( 'Calendar' )
)
return
__total,

ALLEXCEPT(
'Calendar',
'Calendar'[YearWeekNumber]
)
)
)
Frequent Visitor

Your solution looks great, I did not know such kind of syntax with the definition of variables "on the fly" during the CALCULATE  function. I implemented it. The result is correct when I filter the page with a week num (or severals like you said) coming from the Calendar table but force me to present a long list of all weeks of the calendar to the user.
If I try to filter by the week num coming from the Commande then the result is always blank. Is there a good reason for this?
Regards

Solution Sage

You should NEVER use a field that comes directly from a fact table. Fact tables should be hidden, or, if you have measures in them, all their columns must be hidden. Slicing must only be possible through dimensions. ALWAYS. Do it differently and you'll be doomed. If you want to present only weeks that exist in your fact table, there are much better ways to do it. A slicer's entries can be filtered as well by any measure you want. Just use the Filter Pane.

One last thing... your fact table should only contain keys to dimensions and numbers (raw measures). Nothing else. Do it in a different way and you'll feel the heat in no time.

Frequent Visitor

HI @daxer-almighty

I need to change my model to use an Objectif table not per month but per day.
So now the Objectif table look like :

OBJECTIF :
[date]
[Objective]
What do I need to change in the previous solution to retrieve the Objective of the month?  Because now the solution below give me the entire Objective of the year :

Objectif mois =
DISTINCT( 'Calendar'[Year] ),
CALCULATE(
var __firstMonth =
MIN( 'Calendar'[YearMonthnumber] )
var __total =
CALCULATE(
SUM( Objectif[Objectif] ),
'Calendar'[YearMonthnumber] = __firstMonth,
ALL( 'Calendar' )
)
return
__total,
ALLEXCEPT(
'Calendar',
'Calendar'[YearWeekNumber]
)
)
)

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors