Help with sales measure between two dates for custom calendar

[alaynanich]

Hi, I know this is a common ask but I'm stuck on this Dax measure. Basically, I need to calculate the sales variance betwen two dates in my date table for any slice/dimension my user needs. I'm used to using the dateadd or sameperiodlastyear functions for this, but I'm stuck since I'm using custom dates for our new fiscal year calendar.

My sales table comes in weekly with a week start column, and a list of stores and products

For my date table, I added a "this year" and 'last year" column for the comparison dates. Corpweekstart is connected to my date in this table.

I tried the below DAX for YOY sales which works perfeclt in a weekly view. But it appears blank when i look at it in total or as years. From what I've read, it's because of SELECTEDVALUE() and I think i need to use HASONEVALUE() or ISINSCOPE() instead? But I'm not sure if that's correct or what field to use it with?

VAR ty = SUM(Sales_Agg[sales])

VAR PrevWeekStart = SELECTEDVALUE ( 'adhoc_ace_date'[ly_week_start] )

VAR ly =

    IF (

        NOT ISBLANK ( PrevWeekStart ),

        CALCULATE ( SUM(Sales_Agg[sales]), TREATAS ( { PrevWeekStart }, 'adhoc_ace_date'[ace_date] ) ),

        BLANK ()

    )

RETURN IF (ISBLANK( ly ), BLANK (), (ty - ly))

It works fine when I look at it weekly, but not yearly (or any other dimension). 

[FreemanZ · Accepted solution]

hi @alaynanich ,

try like:

YoY=

SUMX(

VALUES('adhoc_ace_date'[ace_date] ),

CALCULATE(

VAR ty = SUM(Sales_Agg[sales])

VAR PrevWeekStart = MAX ( 'adhoc_ace_date'[ly_week_start] )

VAR ly =

    IF (

        NOT ISBLANK ( PrevWeekStart ),

        CALCULATE ( SUM(Sales_Agg[sales]), TREATAS ( { PrevWeekStart }, 'adhoc_ace_date'[ace_date] ) ),

        BLANK ()

    )

RETURN IF (ISBLANK( ly ), BLANK (), (ty - ly))

)

[alaynanich]

Both solutions below worked - thank you!

[v-saisrao-msft]

Hi @alaynanich,

Have you had a chance to review the solution we shared by @FBergamaschi @FreemanZ  @cengizhanarslan ? If the issue persists, feel free to reply so we can help further.

Thank you.

[cengizhanarslan · Accepted solution]

Please try the formulas below:

Sales TY =
SUM ( Sales_Agg[sales] )

Sales LY =
CALCULATE(
    [Sales TY],
    TREATAS(
        VALUES ( 'adhoc_ace_date'[ly_week_start] ),
        'adhoc_ace_date'[ace_date]
    )
)

YoY Variance =
VAR ty = [Sales TY]
VAR ly = [Sales LY]
RETURN
IF ( ISBLANK(ly), BLANK(), ty - ly )

[FreemanZ · Accepted solution]

hi @alaynanich ,

try like:

YoY=

SUMX(

VALUES('adhoc_ace_date'[ace_date] ),

CALCULATE(

VAR ty = SUM(Sales_Agg[sales])

VAR PrevWeekStart = MAX ( 'adhoc_ace_date'[ly_week_start] )

VAR ly =

    IF (

        NOT ISBLANK ( PrevWeekStart ),

        CALCULATE ( SUM(Sales_Agg[sales]), TREATAS ( { PrevWeekStart }, 'adhoc_ace_date'[ace_date] ) ),

        BLANK ()

    )

RETURN IF (ISBLANK( ly ), BLANK (), (ty - ly))

)

[GeraldGEmerick]

@alaynanich SELECTEDVALUE will return blank by default if there are multiple values available (like in a total row). You can try using MAX or MIN instead.

[alaynanich]

@GeraldGEmerick Oh wow this was so simple to remove the blanks. Thanks!!! But the total row is still blank at the bottom of the tables - is there another simple fix for this?

[FBergamaschi]

Hi @alaynanich,

at the total I am not sure to get the meaning of the calculation and a YOY is undefined when there are multiple years.

What would be the meaning you want to give to that value at the matruix total? Then we can fix the DAX

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

[alaynanich]

@FBergamaschi Hi, for the total YOY I want it to dynamically sum up whatever context is in the visual if that makes sense?  So on a weekly level, total the YOY for this year's sales vs the associated week from last year. On an annual level, full 2025 sales vs full 2024 sales. 

[FBergamaschi]

OK in this case I am not sure shy it still does not work

Can you share the pbix via private message so I can check it?

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI