Forum Discussion

kev_sav's avatar
kev_sav
Frequent Visitor
3 years ago
Solved

SAMEPERIODLASTYEAR based on text column

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.

 

 

  • FreemanZ's avatar
    FreemanZ
    3 years ago

    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:

2 Replies

  • FreemanZ's avatar
    FreemanZ
    Super User
    3 years ago

    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:

    • kev_sav's avatar
      kev_sav
      Frequent Visitor
      3 years ago

      Thanks a lot. This solution worked!