Dynamics F/SCM Customer Experience (CRM) Dynamics GP Dynamics Business Central (NAV) Dynamics SL Power Platform Fabric Copilot

[Andy Robinson]

Hi all,

I’m trying to compare sales values last to date against this year to date.
I have a table called Sales and a date table called Calendar. They are linked on Order Date from Sales to Date in Calendar, single from Calendar to Sales.

If I use the following Measure, it works:

LYTD Sell Value = TOTALYTD( SUM(Sales[Line Value]), DATEADD(Sales[Order Date], -365, DAY))

UNTIL I filter on any of the fields in the Sales table, which don’t have a sale exactly 365 days ago.

But I know I should be using the Calendar date field, so I tried:

LYTD Sell Value = TOTALYTD( SUM(Sales[Line Value]), DATEADD(Calendar[Date], -365, DAY))

Yet this doesn’t work at all.

Does anyone have any suggestions? Anything would be greatly appreciated.

thanks

——————————
Andy
——————————

[Lorenzo Vercellati]

Hi,

Total Sell Value = SUM(Sales[Line Value]
YTD Sell Value= TOTALYTD([Total Sell Value], Calendar[Date])
LYTD Sell Value = CALCULATE([YTD Sell Value], SAMEPERIODLASTYEAR(Calendar[Date]))

Regards
Lorenzo

——————————
Lorenzo Vercellati
PowerBI Solution Architect
SolidQ
Milan
3382819866
——————————
——————————————-

[Vishesh Jain]

Hi ,

Just a side-note for the DATEADD() function. It is tricky function and I try to avoid using it.
For DATEADD() you need to have all the calendar dates that are in your calculation, in your calendar table. If your result dates do not exist in your calendar table, it will give an error.

So try avoiding it and rather just subtract? the number of days directly from the date.

However, the solution that Lorenzo provided is the best way to go.

Thank you,

——————————
Vishesh Jain
Owner
VR Construction
——————————
——————————————-

[Andy Robinson]

thank you very much. really appreciate the help

——————————
Andy Robinson
——————————
——————————————-