Conditional YTD previous year measure with date at the period level

[meddojeddo]

I have a fact table showing sales values at the store level by period in the format yyyy0n(202001,202002,202003, etc.) as a numeric field, with another column for year. I have a table chart with year as the dimensionand I want a column displaying sales for the previous year. However, for the current year row, I want it to display last year's YTD value, up to the current period. I have tried to use several different calculations, involving if() conditions, along the lines of this:

Prior YEAR/YTD = if(max(table1[Year]=VALUE(YEAR(TODAY())),CALCULATE(SUM(table1[sales]),FILTER(ALL(table1[Year]),table1[Year]=VALUE(YEAR(TODAY()))-1),table1[PERIOD],table1[PERIOD]<=max(table1[Period])-100,CALCULATE(sum([sales]),filter(ALL(table1[Year]),table1'[Year]=max(table1[Year])-1))))

But I keep getting errors either "DAX comparison operations do not support comparing values of type Text with values of type" or "A function 'CALCULATE' has been used in a True/False expression that is used as a table filter expression. This is not allowed." What would be the best approach to accomplishing this?

[v-xicai]

Hi @meddojeddo ,

You may create a calendar table first of all, then create relationship with your fact table on the date field.

Calendar= CALEANDARAUTO()

Then you may create measures like DAX below.

YTD for CY = TOTALYTD(SUM(Table1[Sales]),Calendar[Date])



YTD for PY = CALCULATE(SUM(Table1[Sales]),SAMEPERIODLASTYEAR(Calendar[Date]))

Best Regards,

Amy 

Community Support Team _ Amy

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

[DataInsights]

@meddojeddo,

My approach would be to parse the yyyy0n field, and create a date field (calculated column, using the first day of the month) in the fact table (e.g., 2020-01-01, 2020-02-01). Then create a date table, and join the two tables. This will enable you to utilize time intelligence functions.