topic Re: Help with sales measure between two dates for custom calendar in DAX Commands and Tips

Help with sales measure between two dates for custom calendar

alaynanich — Fri, 09 Jan 2026 19:41:31 GMT

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

Re: Help with sales measure between two dates for custom calendar

GeraldGEmerick — Fri, 09 Jan 2026 19:59:34 GMT

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.

Re: Help with sales measure between two dates for custom calendar

alaynanich — Fri, 09 Jan 2026 20:07:45 GMT

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?

Re: Help with sales measure between two dates for custom calendar

FBergamaschi — Fri, 09 Jan 2026 20:17:14 GMT

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

Re: Help with sales measure between two dates for custom calendar

alaynanich — Fri, 09 Jan 2026 20:25:29 GMT

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.

Re: Help with sales measure between two dates for custom calendar

FreemanZ — Fri, 09 Jan 2026 21:38:03 GMT

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

)

Re: Help with sales measure between two dates for custom calendar

cengizhanarslan — Sat, 10 Jan 2026 06:50:14 GMT

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 )

Re: Help with sales measure between two dates for custom calendar

FBergamaschi — Sat, 10 Jan 2026 11:31:53 GMT

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

Re: Help with sales measure between two dates for custom calendar

v-saisrao-msft — Fri, 16 Jan 2026 04:32:33 GMT

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.

Re: Help with sales measure between two dates for custom calendar

alaynanich — Fri, 16 Jan 2026 21:10:22 GMT

Both solutions below worked - thank you!