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.

[Essjay]

@v-jtian-msft : Thank you for replying. I could actually achieve the above by creating a view in Database that horizontally joins standard and fiscal calendar.

SamePeriodLastYear works like a charm for period level comparisons but not so for week level. 

At week level, it is giving incorrect values when a week is getting split between periods.

For e.g:

In Fiscal year 2022, week 09 is part of fiscal period 03 but for fiscal year 2023, week 09 is part of period 02.

So when I pull Fiscal week number in visual, it gives wrong illusion. For 2023 week 09 , the YA value is 0 though the data exists for week 09 in 2022.

I understand that DAX time intelligence functions don't work at week granularity. 

I want to know if there is a way that one DAX measure can calculate Year Ago metric for both period(month) level and week level without the above discrepancy.

[v-jtian-msft]

Hi,@Essjay .Thank you for your reply .
I'm glad you were able to find your own alternate solution for unifying two different calendar tables in your database!
Here are my other suggestions
As you mentioned, the dax time intelligence function does not work at weekly granularity.
If you want to implement this in power BI
I recommend you to calculate the weeks manually
Create a metric that calculates the difference between the current weekly number and the weekly number of the same period last year, and then calculate the "last year value" based on the difference.
For example:

Sales_Last_Year_Manual = 
VAR CurrentWeek = WEEKNUM(TODAY())
VAR LastYearWeek = WEEKNUM(TODAY()) - 52
RETURN
CALCULATE(
    [Total Sales],
    FILTER(
        ALL('Date'),
        'Date'[WeekNum] = LastYearWeek
    )
)

Note that this manual setting of -52 is subject to error, as a year is generally 52 weeks, so this method will work for most cases, but for leap and non-leap years there is an error in calculating it this way.

So you can add an ISO weeks column to calculate the ISO weeks for each date

like this:

ISO_weekNum=ISOWEEKNUM('Date'[Date])

Data processing when it is calculated that there are 53 weeks in a year (e.g. 2024 is a leap year)

Sales_lastyear_ISO = 
VAR CurrentISOWeek = ISOWEEKNUM(TODAY())
VAR LastYearISOWeek = 
    IF(
        CurrentISOWeek > 52,
        CurrentISOWeek - 52,
        CurrentISOWeek - 52 + 53
    )
RETURN
CALCULATE(
    [Total Sales],
    FILTER(
        ALL('Date'),
        'Date'[ISO_Week_Number] = LastYearISOWeek
    )
)

In fact, if you are able to implement the processing of the data in the database, i.e. using the creation of views as you proposed to achieve the above effect, I think that this in itself is a better solution, because by creating views in the database, you can ensure that there is only a single unified table of dates in the data model, so that you do not need to take into account the differences between different calendars when performing the DAX calculations.
This simplifies the data model, imports data that is processed by the implementation, and reduces the need to deal with multiple calendar tables in Power BI. Reduces the complexity of writing DAX.
Ensure data consistency and ensure that you do not need to create other reports in the future to harmonize the dates of the two calendar tables you use again.
Avoid calculation errors caused by different calendars.

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.

[Essjay]

@v-jtian-msft Thank you for the detailed explanation on weekly Year ago calculations! We already have a 52 Weeks Ago Metric in our model that is working fine at week level but then when rolling up to period(month) level, it sometimes doesn't align to actual year ago period value because of difference in number of weeks in same period last year.

The requirement from business team is to have one measure that calculates Year Ago Sales correctly for both weekly & monthly granularity when drilling down from period to week level and also when rolling up from week to period level!

I am able to calculate the Year Ago's correctly for both granularities but by using two metrics, one at weekly granularity & one at monthly granularity.

I really want to know if it's possible to combine these two granularities in one measure on PBI end and calculate correctly the Yea Ago's while rolling up and drilling down!

Thank you in advance!!

[v-jtian-msft · Accepted solution]

Hi,@Essjay . Thank you for your reply.
According to your description, your team has previously implemented what I suggested and the question now is how to combine the two MEASURES you have created into one MEASURES based on the difference in filtering granularity, between your mentioning of hierarchical drilling up/down, I assume that you are implementing the field drilling functionality through a matrix.
If my understanding is correct and your current requirement is to combine two measures that are already working properly but with different granularity of computation into one measure
You can refer to my suggestion below:
Use the switch()/IF() functions with ISINSCOPE/ISFILTERED/HASONEVALUE()
ps: all three functions can be used, and they achieve similar results in matrices!
Here is a test:

You can determine what date level is being filtered under the current level by categorizing the discussion, and if it is weekly granularity, perform measure1
If it's monthly granularity, run measure2.
This will merge the two measures you created earlier into a single measure.
like this:

M_result=
SWICH(TRUE(),
  HASONEFILTER([week_level]),[measure1],
  HASONEFILTER([month_level]),[measure2]
)

Could you provide a .pbix file that does not contain sensitive data, if so, this would be helpful in solving 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.

[Essjay]

@v-jtian-msft Thank you for the idea of using Switch to check for filtering granularity!

It kinda worked! I am able to switch as per matrix granularity!

The only concern is tripling the effort! To meet business requirement we need to create two base metrics then one final metric!

Moreover, there are quite a few such measures of same nature!!! So its a lot of effort and dependency!

But I am glad there is a way to meet the requirement!

Thanks!!1

[v-jtian-msft]

Hi,@Essjay .Thank you for your reply.
I'm glad to see that my suggestion can help you, in fact you can also merge the two measures you created before into the final measure by means of the var variable, but this will reduce the readability, in fact the measure is not equivalent to a calculate column, he will not take up the actual memory space of the power bi It doesn't take up any real memory space in power bi, and only has an effect if it is displayed in visual or in use. So the only disadvantage is to write two more measures, but actually does not have some bad effects on the performance of the report.