https://drive.google.com/file/d/1sXxowxdHFdSkUAI5z8vM8Bw48Lj6O20u/view?usp=sharing hiplease 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

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.

YOY Measure with Filter | Microsoft Fabric Community

8 Replies

bideveloper555
Helper IV
4 years ago
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.
bideveloper555
Helper IV
4 years ago
AlexisOlson amitchandak
super heros any help.

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
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.

Forum Discussion

YOY Measure with Filter

8 Replies

Related content

Dynamic measure filtering

Filter by Rank measure

Using measure as a filter

Measure not affected by filter

DAX MEASURE TO FILTER