Calculate on lower level - and then aggregate the results

[LeoST]

Hello all,

I've the following (simplified) usecase:

I want to know if a product sale increased in a month. For that I've a measure that gets me the current month and another for the previous month. Then the third just returns one if there was an increase (let's call it "Detector").
Now when I add my date hierarchy (Year and Month) and the Detecor measure it works fine on a monthly level. However on a yearly level I want to know if in that year I had a month with an increase. So here comes the issue: My measure for that isn't working. On a yearly base its always just blank?

Detecor2 = 
IF (
    ISINSCOPE ( SalesDate[SDate].[Month] ),
    [Detecor],
    IF (
        ISINSCOPE ( SalesDate[SDate].[Year] ),
        maxx(SUMMARIZE(SalesDate,SalesDate[SDate].[Month],"Dect",[Detecor]), [Dect])
        0
    )
)

[LeoST · Accepted solution]

Got it to work by removing the measure from the summarize and using it like this:

Detecor2 = 
IF (
    ISINSCOPE ( SalesDate[SDate].[Month] ),
    [Detecor],
    IF (
        ISINSCOPE ( SalesDate[SDate].[Year] ),
        maxx(SUMMARIZE(SalesDate,SalesDate[SDate].[Month]), [Detecor])
        0
    )
)

[Anonymous]

Hi @LeoST ,

Please try this measure:

Detector2 = 
IF (
    ISINSCOPE ( SalesDate[SDate].[Month] ),
    [Detecor],
    IF (
        ISINSCOPE ( SalesDate[SDate].[Year] ),
        CALCULATE (
            MAXX (
                SUMMARIZE (
                    SalesDate,
                    SalesDate[SDate].[Year],
                    SalesDate[SDate].[Month],
                    "Dect", [Detecor]
                ),
                [Dect]
            )
        ),
        0
    )
)

This modified measure uses the "SUMMARIZE" function to group the data by year and month and calculate the "Detector" measure at the month level. Then, it uses the "MAXX" function to return the maximum value of the "Detector" measure for each year. Finally, it uses the "CALCULATE" function to evaluate the measure in the context of the year level. This should give you the desired result of detecting if there was an increase in sales for each year.

Best Regards,

Stephen Tao

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

[grazitti_sapna]

Hi, @LeoST 

I guess this is because of the incorrect IF statement 

Try using:-

Detector2 =
IF (
ISINSCOPE ( SalesDate[SDate].[Month] ),
[Detector],
IF (
ISINSCOPE ( SalesDate[SDate].[Year] ),
MAXX(
SUMMARIZE(SalesDate, SalesDate[SDate].[Month], "Dect", [Detector]),
[Dect]
),
0
)
)

Hope this will help you