Forum Discussion

bideveloper555's avatar
bideveloper555
Icon for Helper IV rankHelper 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...
Need Help
Show and Tell
Tips and Tricks
  • Icey's avatar
    Icey
    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's avatar
Icey
Icon for Community Support rankCommunity Support
4 years ago

Hi bideveloper555 ,

 

Could this meet your requirements?

TurnoverAmount MoM% 1 = 
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
    )
VAR _Cur =
    CALCULATE (
        SUM ( 'Table'[Amount] ),
        DATESBETWEEN ( 'Date'[Date], StartDate_Cur, EndDate_Cur ),
        ALL ( 'Date' ),
        'Table'[shopid] IN t4
    )
RETURN
    if(ISFILTERED('Table'[shopid]), DIVIDE ( _Cur - _Pre, _Pre )
)
TurnoverAmount MoM% 2 = 
SUMX ( VALUES ( 'Table'[shopid] ), [TurnoverAmount MoM% 1] )

 

 

Best Regards,

Icey

 

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