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

[Nick 2]

Hi,

Trying to calculate to date monthly sales vs to date 12 prev monthly but I cannot get it to work

=calculate(sales MTD),filter(‘Date’,’Date'[Date] <TODAY()),DATEADD(‘Date’,’Date'[Date],-12,MONTH)) 

Comes back blank

If I try this

calculate([Total Sales],dateadd(filter(datesmtd(”Date’,’Date'[Date]),’Date’,’Date'[Date] <TODAY()),-12, MONTH))

It somewhat works but I get this

I am looking to calculate the last years MTD sales so I can do an analysis 

If someone could help me I would greatly appreciate it

Thanks

——————————
Nick
——————————

[Aubrey McKillop]

Nick,

Are you trying to compare this month to date to last years same month to the same date?

Aubrey

——————————
Aubrey McKillop
Director of IT
Moncton NB
——————————
——————————————-

[Nick 2]

Yes I am

——————————
Nicholas O C
——————————
——————————————-

[Christopher Schnaars]

Hi, Nicholas:

Try these:
Sales MTD = CALCULATE([Total Sales], DATESMTD(‘Date'[Date]))
Sales YTD = CALCULATE([Total Sales], DATESYTD(‘Date'[Date]))

If you still aren’t getting the right numbers, check your table relationships. The fact you keep getting the same number suggests you don’t have your relationships defined correctly, but I can’t tell without being able to see the other columns in your table.

Good luck!

——————————
Christopher Schnaars
Laboratory Testing Inc.
Hatfield, Pennsylvania
——————————
——————————————-

[Nick 2]

Hi,

That does not resolve my issue, I cannot do report against last years figures

What I am looking for is Sales from the start of the month until the current date vs last years month May to today’s date

——————————
Nicholas O C
——————————
——————————————-

[Christopher Schnaars]

Please excuse me for being dense, but let’s try a specific example: Let’s say you’re running a report today. One measure you want is Total Sales from May 1, 2020, through what you have so far for May 11, 2020.

For the second measure, you want to go back to May 1, 2019. Assuming that is correct, what is the end date for that range: May 11, 2019, or May 11, 2020?

——————————
Christopher Schnaars
Laboratory Testing Inc.
Hatfield, Pennsylvania
——————————
——————————————-

[Nick 2]

Yes one measure to run a report from 1 May 2020 – 11 May 2020
Second measure to run a report from 1 May 2019 – 11 May 2019

Thank you

——————————
Nicholas O C
——————————
——————————————-

[Christopher Schnaars]

OK, well I believe my first formula listed above should get you Month To Date sales for the current month. To get the same period from the previous year, try this (PYMTD is my abbreviation for Prior Year Month To Date:
Total Sales PYMTD = CALCULATE([Total Sales], DATESBETWEEN(‘Date'[Date], DATEADD(STARTOFMONTH(‘Date'[Date]), -1, YEAR), DATEADD(‘Date'[Date], -1, YEAR)))

——————————
Christopher Schnaars
Laboratory Testing Inc.
Hatfield, Pennsylvania
——————————
——————————————-

[Aubrey McKillop]

Here is the formula I use in my reports 

CALCULATE([Total Parts],

FILTER(‘Calendar’,’Calendar'[Date] >= ‘Calendar'[FDLYSM]),

FILTER(‘Calendar’,’Calendar'[Date] < ‘Calendar'[SDLY])

)

for a bit of background in my date table I have a column FDLYSM (First Day Lay Year Same Month) and SDLY (Same Day Last Year)

I use these in the formula to create the above measure

Aubrey

——————————
Aubrey McKillop
Director of IT
Moncton NB
——————————
——————————————-