topic Next item of the base field in DAX Commands and Tips

Next item of the base field

ssaintlouis — Sun, 17 Mar 2024 17:43:03 GMT

I got that with that measure : CALCULATE(sum(BalanceVerification[MENSUEL]),'Dim_rubriquecptes'[Type compte]="REVENUS",
ALLEXCEPT(BalanceVerification,BalanceVerification[EXERCICE_Name]))

How to get That ?

Re: Next item of the base field

Anonymous — Mon, 18 Mar 2024 02:52:23 GMT

Hi ssaintlouis ,

You can use the SAMEPERIODLASTYEAR() function.

Measure2 =CALCULATE ( [Measure], SAMEPERIODLASTYEAR ( 'Date'[Date] )

In order to use any time intelligence calculation, you need a well-formed date table. For more details, please refer:

SAMEPERIODLASTYEAR – DAX Guide

Differences between DATEADD and PARALLELPERIOD in DAX - SQLBI

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

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

Re: Next item of the base field

ssaintlouis — Mon, 18 Mar 2024 17:32:31 GMT

Hi,

No, I don't have date field. The field BalanceVerification[EXERCICE_Name] is in text format. I want to show the next category for each item category in the field BalanceVerification[EXERCICE_Name] (Ex. 2022-2023, Ex. 2021-2022, Ex. 2020-2021, etc.) as I said.
What can I change in this measure?
ContRev := CALCULATE(sum(BalanceVerification[MENSUEL]),'Dim_rubriquecptes'[Type compte]="REVENUS",
ALLEXCEPT(BalanceVerification,BalanceVerification[EXERCICE_Name]))

I work with Power Pivot in Excel 365.

Re: Next item of the base field

Anonymous — Tue, 19 Mar 2024 08:41:21 GMT

Hi ssaintlouis ,

We can create measures.

Measure 1 = INT(RIGHT(MAX(BalanceVerification[EXERCICE_Name]),4))

Measure 2 = var a=[Measure 1]-1 return CALCULATE([ContRev],FILTER(ALLSELECTED(BalanceVerification),[Measure 1]=a))

Then the result is as follows.

Best Regards,

Neeko Tang

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

Re: Next item of the base field

ssaintlouis — Wed, 20 Mar 2024 05:02:10 GMT

I got Test1 with this measure
Test1:=CALCULATE([ContFact],ALLEXCEPT(Table1,Table1[Item]))
How about Test2
Test1 is the total of each item. I want in Test2 the total of the next Item like this

Re: Next item of the base field

Anonymous — Wed, 20 Mar 2024 06:49:17 GMT

Hi ssaintlouis ,

This is my test data.

We can create two measures.

index = RANKX(ALLSELECTED('Table1'),[Item],MAX('Table1'[Item]),ASC,Dense)

Test2 = var a=[index]+1 return CALCULATE([Test1],FILTER(ALLSELECTED(Table1),[index]=a))

Best Regards,

Neeko Tang

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