Forum Discussion

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 ,

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.

Template - Copy.pbix181 KB

bideveloper555

Helper IV

4 years ago

That is one hell of measure.

Thank you for the hard work and i really appreciate for the measure you wrote.

please see the below image.

in jan 2021, store s1, & s5 are the only 2 stores have sales in 2020,2021.

s1 from 50 to 100 and s2 200 to 200.

but actually total in 2020 is 250 and 2021 is 300 than growth will be 20% which is what am expecting.

your measure are working in indivisual level but i need to show on card as 20%.

every other store was excluded because, they dont have sales in current month & last year month.

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