Helper IV

4 years ago

Solved

YOY Measure with Filter

https://drive.google.com/file/d/1sXxowxdHFdSkUAI5z8vM8Bw48Lj6O20u/view?usp=sharing hi please find the attached sample. am working on dash board. Criteria for dashboard: IF in current month or p...

4 years ago

Hi bideveloper555 ,

Something like this?

TurnoverAmount MoM% =
VAR _Cur_Pre =
    SUMX (
        VALUES ( 'Table'[shopid] ),
        IF (
            [_Cur_TurnoverAmount MoM%] * [_Pre_TurnoverAmount MoM%] <> 0,
            [_Cur_TurnoverAmount MoM%] - [_Pre_TurnoverAmount MoM%]
        )
    )
VAR _Pre =
    SUMX (
        VALUES ( 'Table'[shopid] ),
        IF (
            [_Cur_TurnoverAmount MoM%] * [_Pre_TurnoverAmount MoM%] <> 0,
            [_Pre_TurnoverAmount MoM%]
        )
    )
RETURN
    DIVIDE ( _Cur_Pre, _Pre )

Best Regards,

Icey

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

Icey

Community Support

4 years ago

Hi bideveloper555 ,

Please try this method first, I haven't worked out a simpler method yet.

_Cur_TurnoverAmount MoM% = 
VAR StartDate_Cur =
    MINX ( DATEADD ( 'Date'[Date], - [MoM Count Value] + 1, MONTH ), [Date] )
VAR EndDate_Cur =
    MAX ( 'Date'[Date] )
VAR StartDate_Pre =
    MINX ( DATEADD ( 'Date'[Date], -12 -  [MoM Count Value] + 1, MONTH ), [Date] )
VAR EndDate_Pre =
    MAXX ( DATEADD ( 'Date'[Date], -1, YEAR ), [Date] )
VAR t =
    SUMMARIZE (
        FILTER (
            ALL ( 'Date'[Date], 'Date'[MonthInCalendar] ),
            ( 'Date'[Date] >= StartDate_Cur
                && 'Date'[Date] <= EndDate_Cur )
                || ( 'Date'[Date] >= StartDate_Pre
                && 'Date'[Date] <= EndDate_Pre )
        ),
        [MonthInCalendar]
    )
VAR t1 =
    CROSSJOIN ( t, VALUES ( 'Table'[shopid] ) )
VAR t2 =
    FILTER (
        ADDCOLUMNS (
            t1,
            "Sum_",
                CALCULATE (
                    SUM ( 'Table'[Amount] ),
                    FILTER (
                        ALL(  'Date' ),
                        [MonthInCalendar] = EARLIER ( 'Date'[MonthInCalendar] )
                            && [shopid] = EARLIER ( 'Table'[shopid] )
                    )
                )
        ),
        [Sum_] <> BLANK ()
    )
VAR t3 =
    FILTER (
        ADDCOLUMNS ( t2, "Count_", COUNTROWS ( t2 ) ),
        [Count_] =  [MoM Count Value] * 2
    )
VAR t4 =
    SUMMARIZE ( t3, [shopid] )
VAR _Cur =
    CALCULATE (
        SUM ( 'Table'[Amount] ),
        DATESBETWEEN ( 'Date'[Date], StartDate_Cur, EndDate_Cur ),
        ALL ( 'Date' ),
        'Table'[shopid] IN t4
    )
RETURN
    _Cur

_Pre_TurnoverAmount MoM% = 
VAR StartDate_Cur =
    MINX ( DATEADD ( 'Date'[Date], - [MoM Count Value] + 1, MONTH ), [Date] )
VAR EndDate_Cur =
    MAX ( 'Date'[Date] )
VAR StartDate_Pre =
    MINX ( DATEADD ( 'Date'[Date], -12 -  [MoM Count Value] + 1, MONTH ), [Date] )
VAR EndDate_Pre =
    MAXX ( DATEADD ( 'Date'[Date], -1, YEAR ), [Date] )
VAR t =
    SUMMARIZE (
        FILTER (
            ALL ( 'Date'[Date], 'Date'[MonthInCalendar] ),
            ( 'Date'[Date] >= StartDate_Cur
                && 'Date'[Date] <= EndDate_Cur )
                || ( 'Date'[Date] >= StartDate_Pre
                && 'Date'[Date] <= EndDate_Pre )
        ),
        [MonthInCalendar]
    )
VAR t1 =
    CROSSJOIN ( t, VALUES ( 'Table'[shopid] ) )
VAR t2 =
    FILTER (
        ADDCOLUMNS (
            t1,
            "Sum_",
                CALCULATE (
                    SUM ( 'Table'[Amount] ),
                    FILTER (
                        ALL(  'Date' ),
                        [MonthInCalendar] = EARLIER ( 'Date'[MonthInCalendar] )
                            && [shopid] = EARLIER ( 'Table'[shopid] )
                    )
                )
        ),
        [Sum_] <> BLANK ()
    )
VAR t3 =
    FILTER (
        ADDCOLUMNS ( t2, "Count_", COUNTROWS ( t2 ) ),
        [Count_] =  [MoM Count Value] * 2
    )
VAR t4 =
    SUMMARIZE ( t3, [shopid] )
VAR _Pre =
    CALCULATE (
        SUM ( 'Table'[Amount] ),
        DATESBETWEEN ( 'Date'[Date], StartDate_Pre, EndDate_Pre ),
        ALL ( 'Date' ),
        'Table'[shopid] IN t4
    )

RETURN
    _Pre

TurnoverAmount MoM% =
VAR _Cur_Pre =
    SUMX (
        VALUES ( 'Table'[shopid] ),
        [_Cur_TurnoverAmount MoM%] - [_Pre_TurnoverAmount MoM%]
    )
VAR _Pre =
    SUMX ( VALUES ( 'Table'[shopid] ), [_Pre_TurnoverAmount MoM%] )
RETURN
    DIVIDE ( _Cur_Pre, _Pre )

Best Regards,

Icey

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

Template - Copy (1).pbix182 KB

bideveloper555

Helper IV

4 years ago

👏👏👏

That was one hell of measure.

thank again for all the hard work.

working like a beauty.

one small issue i have noticed.

if user selects multiple months, if any stores has sales in 2 months, that store is elimintaed in measure.

when jan 2021 was selected.

when jan & Feb 2021 was selected.

tbh, that was impressive the work you have done.

GREAT JOB ICEY

Icey

Community Support

4 years ago

Hi bideveloper555 ,

Something like this?

TurnoverAmount MoM% =
VAR _Cur_Pre =
    SUMX (
        VALUES ( 'Table'[shopid] ),
        IF (
            [_Cur_TurnoverAmount MoM%] * [_Pre_TurnoverAmount MoM%] <> 0,
            [_Cur_TurnoverAmount MoM%] - [_Pre_TurnoverAmount MoM%]
        )
    )
VAR _Pre =
    SUMX (
        VALUES ( 'Table'[shopid] ),
        IF (
            [_Cur_TurnoverAmount MoM%] * [_Pre_TurnoverAmount MoM%] <> 0,
            [_Pre_TurnoverAmount MoM%]
        )
    )
RETURN
    DIVIDE ( _Cur_Pre, _Pre )

Best Regards,

Icey

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

Forum Discussion

YOY Measure with Filter

Related content

Dynamic measure filtering

Filter by Rank measure

Using measure as a filter

Measure not affected by filter

DAX MEASURE TO FILTER