topic Re: How to get the values of the past year in DAX Commands and Tips

How to get the values of the past year

FilipHanus — Thu, 20 Jun 2024 07:00:10 GMT

Hello,

I have this table.

In this table, I have values for the selected year 2025. For this year, it is the measure "Total Tržby SF nápočet" next to it I have values for the year 2024 measure "Total Tržby SF nápočet Y-1 ". What I need to get is that in the third measure "Total Tržby SF nápočet Y-1 2"I need to get the values from row 2023_06 from the measure "Total Tržby SF nápočet Y-1" for row 2024_06 Does anyone know how to do this please? I have already tried many measures but none of them gives the right result.

Re: How to get the values of the past year

lbendlin — Fri, 21 Jun 2024 00:23:41 GMT

SAMEPERIODLASTYEAR will work for that.

Re: How to get the values of the past year

FilipHanus — Fri, 21 Jun 2024 06:37:48 GMT

That won't work. It can be applied to a contiguous series of data, but this is not a contiguous series of data. This is a combination of two dates: Year - sameperiotlastyear can be used, but it cannot be applied to the second date.

Re: How to get the values of the past year

AntrikshSharma — Fri, 21 Jun 2024 07:10:55 GMT

FilipHanus Yes but you don't need to worry about contiguous data if you have a proper Date table and a relationship to the other table, SAMEPERIODLASTYEAR would work fine then.

Re: How to get the values of the past year

FilipHanus — Fri, 21 Jun 2024 08:13:29 GMT

I have a date table, but there is a relationship between sales and the date table. Column Year Month UZ OPP is a different table and it is not possible to do another realice because there would be ambiguity. Model is here. I am working with two dates. One date is date of sales and the second date is date of close opportunity.

Re: How to get the values of the past year

lbendlin — Fri, 21 Jun 2024 13:57:17 GMT

Avoid linking fact tables directly. Prefer to control them via dimension tables (like your calendar table). Use USERELATIONSHIP and SAMEPERIODLASTYEAR as needed.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Re: How to get the values of the past year

FilipHanus — Mon, 24 Jun 2024 07:35:32 GMT

Report SalesForce Community.pbix Here is a link to the source file. The goal is to get in the table for line 2024_06 in the measure "Total Revenue SF calculated y-1 2" the value from line 2023_06 from the measure "Total Revenue SF calculated Y-1" and the same for the other lines. Measure Total Sales SF count Y-1" already shows last year's sales. I still need to shift the opportunity close date by a year.

Re: How to get the values of the past year

FilipHanus — Wed, 26 Jun 2024 06:07:14 GMT

Can anyone help me?

Re: How to get the values of the past year

lbendlin — Thu, 27 Jun 2024 19:15:17 GMT

Total Tržby SF nápočet y-1 2 = CALCULATE([Total Tržby SF nápočet Y-1], SAMEPERIODLASTYEAR('Calendar table'[Date]))

Re: How to get the values of the past year

FilipHanus — Fri, 28 Jun 2024 12:20:37 GMT

try it in the file I sent and you'll see that it just doesn't work!

Re: How to get the values of the past year

lbendlin — Fri, 28 Jun 2024 15:29:49 GMT

I did try and it did seem to work as far as I could tell. Can you provide a more simplified sample file that only focuses on the issue you are trying to solve?

Re: How to get the values of the past year

FilipHanus — Mon, 01 Jul 2024 06:27:04 GMT

OneDrive (sharepoint.com) you can downloand file here

Re: How to get the values of the past year

FilipHanus — Mon, 08 Jul 2024 08:06:43 GMT

Did you manage to find a solution? I have sent you the source file below