YTD vs PYTD by month

[bmbyrnes]

I know there have been a ton of solutions on this but I've tried many of them with no luck. Obviously I'm pretty new to PowerBI.

I'm trying to compare current YTD COGS vs LYTD COGS. My data is organized by month from Jan 2017 to March 2020.

YTD COGS = CALCULATE(SUM(AIT_RAW_Monthly[COGS Monthly]),'Calendar'[Date])

PYTD COGS = CALCULATE([YTD COGS],SAMEPERIODLASTYEAR('Calendar'[Date]))

Even the following expression returns just 2020 YTD (Jan, Feb, March).

YTD COGS = CALCULATE(SUM(AIT_RAW_Monthly[COGS Monthly]),CALENDAR(DATE(2018,1,1),TODAY())

Below are some screenshots of my data. My relationship is YearMonth between COGS table and Calendar table.

Am I missing some relationship between MonthYear to actual date in the calendar table?

COGS table 

Calendar table 

[v-shex-msft]

Hi @bmbyrnes,

Maybe you can take a look at following link about use date function to manually define filter range and rolling calculate, it should more suitable with irregular date records:

Time Intelligence "The Hard Way" (TITHW) 

Regards,

Xiaoxin Sheng

[Anonymous]

The date table generated from your code in Dates_Dim runs from 12/1/2018 - 10/31/2019. Using time intelligence functions with a date table that does not cover the full years can give you wrong answers. Try changing your Dates_Dim code to the following.

Dates_Dim =
ADDCOLUMNS(
CALENDAR (
DATE ( YEAR ( TODAY() ) - 1, 1, 1 ),
DATE ( YEAR ( TODAY() ), 12, 31 )
),
"Day", DAY ( [Date] ),
"Month", MONTH ( [Date] ),
"Month_name", FORMAT ( [Date], "MMM" ),
"Year", YEAR ( [Date] )
)

[amitchandak]

@bmbyrnes 

The YTD formula should use dateytd or totalytd

example

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"12/31"))
This Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD((ENDOFYEAR('Date'[Date])),"12/31"))

Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
Last YTD complete Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))

Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

[bmbyrnes]

Those expressions don't seem to work.

This expression returns JUST November and December 2019 COGS. Shouldn't it return everything in calendar 2019 UP TO 11/1/2019? It appears the expression is doing the YTD COGS from December and moves backwards in time.

YTD COGS = CALCULATE(SUM(AIT_RAW_Monthly[COGS Monthly]),DATESYTD('Calendar'[Date],"11/1/2019"))

This expression returns nothing. I wonder if 'Calendar'[Date] is messed up.

PYTD COGS = CALCULATE(SUM(AIT_RAW_Monthly[COGS Monthly]), DATESYTD(DATEADD('Calendar'[Date],-1,YEAR),"12/31"))

[amitchandak]

@bmbyrnes 

If you are using calendar year Jan-Dec , do not use 12/31

DATESYTD(DATEADD('Calendar'[Date],-1,YEAR)))

This basically year-end date end. Means If my year is from Apr to march then I will give "3/31". Means start my year from April for YTD

[vijayvizzu]

Did you tried the DAX Time intelligence function DATESYTD to get the YTD values. Once you achieve this, you can use SAMEPERIODLASTYEAR function to calculate last year values

https://docs.microsoft.com/en-us/dax/datesytd-function-dax

https://docs.microsoft.com/en-us/dax/sameperiodlastyear-function-dax

[bmbyrnes]

Thanks for the reply. I have tried YTD expressions and they don't seem to work.

[bmbyrnes]

I think it's not working because my slicer only has 2019 selected. How do I still calculate PYTD without selected that year too? I don't want to select both years in the slicer and adjust all my visualizations.