Sameperiod Last year for Year, Quarter and Month not working

[damit23183]

Hi,

I have been trying to calculate Count based on Current Year , Fiscal year and same thing for last year.

Everything is working for Calendar Year and Fiscal Year but sameperiod last year, Month and quarters are not working for both Calendar and Fiscal Year Table.

As you can see blue highlihgted, 

Calendar Year: In Calendar Year table, I am trying to calculate Count for Last year same period but you can see the value is going into 2021 which should not happend. If its same period last year then it should be before current year not next year. This has been happening for Quarters and Month also.

Fiscal year: In Fiscal Year table, I am trying to calculate Count for Last year same period but you can see the value is going into 2022 which should not happend. If its same period last year then it should be before current year not next year. This has been happening for Quarters and Month also.

NOTE: Fiscal Year starting from October to September and I have only data upto December 2020. So, 2022 should definately not come.

Formulas I have applied are follows; (Formula are working without error just values are placing in wrong place)

Last Year Same period for Year : Calculate(Count(ID), SAMEPERIODLASTYEAR('Date'[Date]))

                                      Calculate (Count(ID), DATESYTD(dateadd('Date'[Date],-1,YEAR)))

Last Year Same Period for Quarters: Calculate(Count(ID), DatesQTD(Dateadd('Date'[Date],-1,Year)))

Last Year Same Period for Month: Calculate(Count(ID), DatesMTD(Dateadd('Date'[Date],-1,Year)))

Any suggestion and Recommendation will be appreciated!

Thanks

[v-eqin-msft]

Hi @damit23183 ,

According to my understand, you want to display last year's YTD QTD,MTD,right?

Take the Year as an example, you could use the following formula after adding a Year column

Year = [Date].[Year] 

this year =
IF (
    HASONEVALUE ( 'Table'[Year] ),
    CALCULATE (
        SUM ( 'Table'[Cost] ),
        FILTER ( 'Table', [Year] = MAX ( 'Table'[Year] ) )
    ),
    CALCULATE ( SUM ( 'Table'[Cost] ), DATESQTD ( 'Table'[Date] ) )
)

YTD LY =
IF (
    HASONEVALUE ( 'Table'[Year] ),
    CALCULATE (
        SUM ( 'Table'[Cost] ),
        FILTER ( ALL ( 'Table' ), [Year] = MAX ( 'Table'[Year] ) - 1 )
    )
)

My final visualization looks like this:

Here is my pbix file.

Did I answer your question? Please mark my reply as solution ,thank you.

If you have any questions, please upload some insensitive data samples .

Best Regards,

Eyelyn Qin

[damit23183]

Hi,

Thanks for your response really appriciate it.

Yes you are right i would like to calculate count for Last year same period for YEAR, QUARTER and MONTH.

I did try your LAST YEAR Formula but did not work.

For current YEAR, QUARTER and MONTH calculations are working for Calendar as well as Fiscal year.

I am only struggling with sameperiod last year calculation for YEAR, QUARTER and Month.

Thanks

[AllisonKennedy]

@damit23183
Can you please provide your expected result.
From my understanding, SamePeriodLastYear will give value of what the sales were for previous year. So if your total count has data for 2019 and 2020, SamePeriodLastYear will have data for 2020 and 2021. The 2020 SamePeriodLastYear will be the same values as in 2019 Count. The 2021 SamePeriodLastYear will be the same values as in the 2020 Count.

I typically use:

Last Year Calc = CALCULATE( [CountThisYear], DATEADD(Date[Date], -365, Day) )

That way, you only have to calculate it once and it should work for quarter, month, year. You also don't need to recalculate the [CountThisYear] measure, which would just be the existing measure you have that is working (in your example looks like it might be a YTD or QTD measure).

[damit23183]

Hi,

Thanks for your response.

Appreciate your time!

I did try the formula you gave but its not giving me expected result.

My understanding is that if I am calculating Count for SAMEPERIOD LAST YEAR then for calendar I should only data up to2018, 2019 and 2020 because I only data for these two years (2019 & 2020). Please Correct me if I am wrong here on understanding?

Therefore, I should not get data for 2021, correct? (But I am getting data for full 2021 year)

Thanks

[AllisonKennedy · Accepted solution]

@damit23183 

What you are describing is SAMEPERIODNEXTYEAR which doesn't really exist as a function, but you could use DATEADD with positive 12 month difference or ParallelPeriod with positive difference. 

When we do LAST year, if we have data for 2020, the SAMEPERIODLASTYEAR measure will look for data in 2019 - if there was no data, it will be blank. That means that if our dataset has data for 2019 and 2020, your SAMEPERIODLASTYEAR measure will give you a target value for the years AFTER you have data, or the NEXT years, so 2020 and 2021. I know it's a bit counter intuitive that SAMEPERIODLASTYEAR gives results for NEXT years compared to what is in our dataset, but if you look at just 1 year data it makes a bit more sense (sometimes).

So take this example below. I have used model data that has Sales reported for 2019, and 2020 only. Because we don't know the sales for 2018, when we look at January 2019, I do have Sales for that month, but I don't know what the SAMEPERIODLASTYEAR sales were, because in 2019, LASTYEAR = 2018 and we don't have data for 2018. 

When we look at the year 2020, I DO have data for the last year, which is now 2019.

When we look at the year 2021, I also already know what may sales were for the previous year, so essentially we know our target, but we don't yet have the Sales as we haven't gotten to those dates yet.  

Here is using the Sales NEXT year example, which is what you are expecting: 

I will also attach this sample file so you can play with the data, but if it still is unclear, let us know and maybe we can suggest videos or other way to make it clear. 

[damit23183]

Hi,

THank you for the detail level information and really appriciate it.

I have mix up your logic and some from DAX Pattern which help me to resolve what I was looking for.

Thanks