Need Help with Year Ago metric calculation in one measure for two calendars in semantic model

[Essjay]

Hi all,

I want to know if it is possible to create ONE 'Year Ago Metric' (same period last year) using a DAX measure when there are two calendars in the model.

E.g. My model has ONE fact table but TWO calendars- Fiscal and Standard 

Requirement :  We are building a semantic model for business use case which has a requirement to calculate a 'Year Ago' metric like 'Sales Year Ago' which works for both calendars selection (Fiscal & Standard) as per the user requirement.

Is there a way we can create a single measure that can calculate 'Year Ago' value for the metric for both the calendars in model. We don't want to create a dynamic measure  as per user selection of calendar in report because this requirement is not one report specifc rather model specific.

P.S. The datekey column for all the tables in model is of type integer!

Any pointers are appreciated! Thanks!

[v-jtian-msft]

Hi,@Essjay .
I am glad to help you.
According to your description, there are two different calendar tables in your report, and you want to use one measure to filter out data from one year ago in a uniform way.
Here are my suggestions, which I hope will give you some good ideas
1. Create a calendar table to unify the accounting and standard calendars in your model, and in the future, pass filter values directly from this new calendar to filter one year old data corresponding to different calendar types, instead of using your original two calendars.
2. Use the SWITCH/IF function with the SAMEPERIODLASTYEAR function to calculate last year's data.
Here is a simple example:

Sales_Last_Year = 
VAR CalendarType = SELECTEDVALUE('Date'[CalendarType], "Standard")
RETURN
IF(
    CalendarType = "Fiscal",
    CALCULATE(
        [Total Sales],
        SAMEPERIODLASTYEAR('Date'[FiscalDateKey])
    ),
    CALCULATE(
        [Total Sales],
        SAMEPERIODLASTYEAR('Date'[StandardDateKey])
    )
)

By setting a CalendarType column in the unified calendar table 'Date' to mark the accounting calendar and standard calendar values separately.

The above is just a simple idea, if you can provide more information, such as a .pbix file that does not contain sensitive data, it will help to solve your problem.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

Best Regards,

Carson Jian,

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