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.

bideveloper555's avatar
bideveloper555
Icon for Helper IV rankHelper 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.