YOY Measure with Filter

[bideveloper555]

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 previous month of a store doesnt have any sales information, that store sales should not be used in YOY.

Example: Store A has traded in jan 2020 but didnt trade in jan 2021. this sales information should be excluded in YOY calcualtion for jan 2021.(when user select his selection from slicer)

Vice versa,Store B no sales in jan 2020 but sales in jan 2021, this sales will be excluded from YOY.

This applies for MOM (1,3,6 months).

3 month (mom)

If user select march 2021.( jan,feb,mar current total vs jan,feb,mar previous yrs total) if in any of 6 months ,there is no sale record. than that store will excluded in 3 month mom measure.

https://drive.google.com/file/d/1sXxowxdHFdSkUAI5z8vM8Bw48Lj6O20u/view?usp=sharing

[bideveloper555]

hi guys,

any help.

YOY on a card,but store sales should be excluded if previous year or current no sales.

only YOY need to calculate for those store which has both side values.

[Icey]

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.

[bideveloper555]

hi

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]

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.

[bideveloper555]

👏👏👏

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 · Accepted solution]

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.

[bideveloper555]

@AlexisOlson @amitchandak 

super heros any help.

[bideveloper555]

This is my YOY%

TurnoverAmount YoY% =
IF(
ISFILTERED('Date'[Date]),
ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),

VAR __PREV_YEAR =
CALCULATE(
SUM('Tablename'[amount]),
DATEADD('Date'[Date].[Date], -1, YEAR)
)
var __CURRENTYEAR = IF(SUM('Tablename'[amount]) <> BLANK(), CALCULATE(SUM('Tablename'[amount])))
Var _SalesYOY = IF (
NOT ISBLANK ( __CURRENTYEAR )
&& NOT ISBLANK ( __PREV_YEAR ),
__CURRENTYEAR - __PREV_YEAR
)
RETURN

DIVIDE(
_SalesYOY,
__PREV_YEAR
)

)

 if user selects jan 2021 in slicer on dashboard.

i need to exclude by storeid sales, if a store has sales in jan 2021 and doesnt have sales in jan 2020, these sales totals shouldnt be in YOY calcualtion. and if stores has sales in 2020 but doesnt have in jan 2021, should be excluded in yoy.

Table looks as below.

shopid    date              amount

1             2021-01-01   250

2             2021-01-01   100

2             2020-01-01   50

3             2020-01-01   150

so bascially storeid 1 & 3 wont be in YOY% measure.