topic Re: Possibly a Relationship Issue or formula issue - Sales totals vs Sales Reps in DAX Commands and Tips

Possibly a Relationship Issue or formula issue - Sales totals vs Sales Reps

Neyney — Sat, 18 Jan 2025 07:43:34 GMT

I am having some major issues with my dashboard now that we are in the beginning of 2025 for some reason. I don't know if it's a relationship issue, or a formula issue. When I use a table to drag in the Current Year Sales, I get the correct number, but the moment I add any other field such as the sales rep, my sub total is correct, but the details are absolutely not, it seems to be taking the data from the previous year actually.

The formula for the YTD Paid (#) is this

Won Oppty Current (#) = COUNTROWS(

FILTER(

'Open and Won Opportunities Data',

'Open and Won Opportunities Data'[Report Source] = "Won" &&

YEAR('Open and Won Opportunities Data'[Actual Close Date]) = YEAR(MAX('Open and Won Opportunities Data'[Actual Close Date]))

)

The formula for YTD Paid ($) is this:

Won Oppty Current ($) =

CALCULATE(

SUM('Open and Won Opportunities Data'[Total Est. Proposal Amt]),

'Open and Won Opportunities Data'[Report Source] = "Won",

YEAR('Open and Won Opportunities Data'[Actual Close Date]) = YEAR(MAX('Open and Won Opportunities Data'[Actual Close Date]))

)

What could the issue be? Guidance is much appreciated!

Re: Possibly a Relationship Issue or formula issue - Sales totals vs Sales Reps

shafiz_p — Sat, 18 Jan 2025 09:09:35 GMT

Hi Neyney Try with the below code:

Won Oppty Current (#) = CALCULATE( COUNTROWS('Open and Won Opportunities Data'), 'Open and Won Opportunities Data'[Report Source] = "Won", YEAR('Open and Won Opportunities Data'[Actual Close Date]) = YEAR(TODAY()) )

Won Oppty Current ($) = CALCULATE( SUM('Open and Won Opportunities Data'[Total Est. Proposal Amt]), 'Open and Won Opportunities Data'[Report Source] = "Won", YEAR('Open and Won Opportunities Data'[Actual Close Date]) = YEAR(TODAY()) )

Hope this helps!!

If this solved your problem, please accept it as a solutiona and a kudos!!

Best Regards,
Shahariar Hafiz

Re: Possibly a Relationship Issue or formula issue - Sales totals vs Sales Reps

Anonymous — Mon, 20 Jan 2025 08:19:45 GMT

Hi ,

Try using the DAX formula shafiz_p provided.

Besides, when adding any other field such as the sales rep, your sub total is correct, but the details are absolutely not, what detail information is incorrect?

Try to check that the relationship between sales rep field and another table column are correctly set up.

Best Regards,

Wisdom Wu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Possibly a Relationship Issue or formula issue - Sales totals vs Sales Reps

Neyney — Mon, 20 Jan 2025 21:56:45 GMT

My LY YTD SALES formula seems off now. My Current Year calculation (CY) based on the max date available on my dataset is working well, which is 1/11/2025, but when I put Last Year's formula in my visual it's showing me all year to December, but I need to see that data from last Year up to the same period this year. My formula seems off, but I can't figure out what it is. Here is what I am using, I tried different formula's but can't get a proper comparison of up to same data side by side:

LY YTD Sales =

CALCULATE(

[All Sales Sum],

'Paid Date Table'[Date] >= DATE(YEAR(TODAY()) - 1, 1, 1),

'Paid Date Table'[Date] <= DATE(YEAR(TODAY()) - 1, MONTH(TODAY()), DAY(TODAY())),

'Paid Date Table'[IsPast] = TRUE()

)

I tried this one too:

LY YTD Sales =

CALCULATE(

[All Sales Sum],

DATESYTD('Paid Date Table'[Date]

))

I've tried this one too, but I know it's off since it is using same period last year, but need it to calculate up to same period of current year:

PY =

CALCULATE(

[All Sales Sum],

SAMEPERIODLASTYEAR(

DATESYTD('Paid Date Table'[Date])

)

)

I've tried this one too, but doesn't work either:

LY YTD Sales =

CALCULATE(

[All Sales Sum],

'Paid Date Table'[Date] >= DATE(YEAR(MAX('Paid Date Table'[Date])) - 1, 1, 1),

'Paid Date Table'[Date] <= DATE(YEAR(MAX('Paid Date Table'[Date])) - 1, MONTH(MAX('Paid Date Table'[Date])), DAY(MAX('Paid Date Table'[Date])))

)

Re: Possibly a Relationship Issue or formula issue - Sales totals vs Sales Reps

shafiz_p — Tue, 21 Jan 2025 03:13:41 GMT

Hi Neyney It looks like you're trying to compare year-to-date (YTD) sales for the current year with the same period last year that means last year up to the same date as this year. You could try datesbetween to specify the date range from the start of last year to the same date last year as the maximum date in your current dataset. Here is the revised verson, you could try:

LY YTD Sales = CALCULATE( [All Sales Sum], DATESBETWEEN( 'Paid Date Table'[Date], DATE(YEAR(MAX('Paid Date Table'[Date])) - 1, 1, 1), DATE( YEAR(MAX('Paid Date Table'[Date])) - 1, MONTH(MAX('Paid Date Table'[Date])), DAY(MAX('Paid Date Table'[Date])) ) ), 'Paid Date Table'[IsPast] = TRUE() )

Hope this helps!!

Re: Possibly a Relationship Issue or formula issue - Sales totals vs Sales Reps

Neyney — Tue, 21 Jan 2025 21:42:24 GMT

That is exactly correct! I updated the formula to use DATESBETWEEN but it's now showing blank 😕

Re: Possibly a Relationship Issue or formula issue - Sales totals vs Sales Reps

Neyney — Tue, 21 Jan 2025 22:37:53 GMT

What could the issue be? am I not referencing the correct table?