Compute Sum of the month knowing week of the year

[FbBrnr]

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.

Can you please help me to make a good computation for this?

Thanks for your help.
Regards.

[daxer-almighty · Accepted solution]

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

[FbBrnr · Accepted solution]

Hi @daxer-almighty ,

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?
Thanks for your help.
Regards

[daxer-almighty · Accepted solution]

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.

[Anonymous · Accepted solution]

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. 

[themistoklis]

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

[FbBrnr]

Hi @themistoklis,

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

[daxer-almighty · Accepted solution]

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

[FbBrnr · Accepted solution]

Hi @daxer-almighty ,

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?
Thanks for your help.
Regards

[daxer-almighty · Accepted solution]

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.

[FbBrnr]

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]

)

)

)

Thanks for your help