Calc YTD for time period one month prior to current date

[Huskyjimbo]

I want to calculate total units sold  YTD of a specific type of product for a period starting in Jan and ending in the month previous to current month. I need a similar calc for previous years also.

For example:

Total units of Diamondback55250 sold for period between Jan2018-Sept2018 (since current month is Oct)

Total units of Diamondback55250 sold for period between Jan2017-Sept2017 (since current month is Oct)

Total units of Diamondback55250 sold for period between Jan2016-Sept2016 (since current month is Oct)

and when the current month changes to November it should show:

Total units of Diamondback55250 sold for period between Jan2018-Oct2018 (since month would be Nov)

Total units of Diamondback55250 sold for period between Jan2017-Oct2017 (since month would be Nov)

Total units of Diamondback55250 sold for period between Jan2016-Oct2016 (since month would be Nov)

So far I have:

Total Units of Diamondback55250 sold = calculate(sumx('Sales', 'Sales'[Units]), filter('Sales', 'Sales'[Product_Code]= "55250"))

This gives me the totals but it doesn't:

A.) cut off the total a month before current month

and

B.) Give me the total units sold for the same time period of previous years.

Any help would be gretly appreciated. 

[v-lili6-msft]

hi, @Huskyjimbo

You may try to use TOTALYTD Function and TODAY Function to add a measure as below:

Measure = CALCULATE(TOTALYTD([Total Units of Diamondback55250 sold],'Calendar'[Date]),FILTER('Calendar',MONTH('Calendar'[Date])<MONTH(TODAY())))

Result:

Best Regards,

Lin

[Ashish_Mathur · Accepted solution]

Hi,

Try this measure

Measure1=calculate(sum('Sales'[Units]),'Sales'[Product_Code]="55250",DATESBETWEEN(Calendar[Date],DATE(YEAR(TODAY()),1,1),EOMONTH(TODAY(),-1)))

Measure2=calculate(sum('Sales'[Units]),'Sales'[Product_Code]="55250",DATESBETWEEN(Calendar[Date],DATE(YEAR(TODAY())-1,1,1),EOMONTH(EDATE(TODAY(),-12),-1)))

Hope this helps.