topic SAMEPERIODLASTYEAR based on text column in DAX Commands and Tips

SAMEPERIODLASTYEAR based on text column

kev_sav — Wed, 26 Apr 2023 09:54:10 GMT

Hi,

I would like to compute SAMEPERIODLASTYEAR() to understand YoY growth of my revenue numbers. However, my data model doesn't have a date column but it has a fiscal_quarter column which is of "Text" data type. Since SAMEPERIODLASTYEAR() requires a date column as an input how do I go about it?

I tried adding a column in my data "quarter_start_date" (a date data type) which corresponds to each fiscal_quarter value and compute SAMEPERIODLASTYEAR() based on quarter_start_date, but when i use fiscal_quarter column in the slicer to fiter our the quarter it doen't give me the last year data. Please let me know. Thanks.

Re: SAMEPERIODLASTYEAR based on text column

FreemanZ — Thu, 27 Apr 2023 06:52:29 GMT

hi kev_sav

not sure if i fully get you, please try plot a visual with a measure like:

Measure = VAR MaxDate = MAX(data[fiscal_quarter_date]) RETURN CALCULATE( SUM(data[revenue]), data[fiscal_quarter_date]=EDATE(MaxDate, -12), ALL(data) )

tried to verify with expanded data like:

it worked like:

Re: SAMEPERIODLASTYEAR based on text column

kev_sav — Thu, 27 Apr 2023 15:48:11 GMT

Thanks a lot. This solution worked!