Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
BeaBF
Solution Sage
Solution Sage

Row Delta between two years (actual and previous)

Hello! I need your help.

I try to explain it to you simply. I have one column with user IDs and a second column with the year that user is active, like this:

IDs YEAR FLAG
AAA 2021 N
AAA 2022 N
BBB 2021 Y

 

I want to calculate a dynamic Flag that indicates the users who were active the year before the selected one but who are no longer active in the selected one.

By selecting 2022 for example, the user AAA is flagged on N, as he was active both in 2021 and in 2022. The user BBB, on the other hand, will be flagged with Y, as he was active in 2021, the year previous to the filtered one and no longer present in the 2022.

Therefore in visualization we will have a table that must be filtered by the Flag (column or measure) without the Flag field being present in the table and a visual filter on the year.
I emphasize that it must be dynamic, in the sense that by filtering 2022, the comparison must only be on 2021, 2020 must not be taken into consideration.

I am attaching a pbix with an example dataset. In the pbix, filtering on 2022, the only ID to appear must be 458925.

Thx u,

BBF

1 ACCEPTED SOLUTION
v-rongtiep-msft
Community Support
Community Support

Hi @BeaBF ,

Plaese refer to my pbix file to see if it helps you.

Create another table with year column. Then put the column into the slicer.

Then create  measures.

Measure = var _1=SELECTEDVALUE('Table (2)'[year])
var _1re=
IF(_1-1=SELECTEDVALUE('Table'[YEAR]),1,0)
return _1re
Measure 2 = var _1= IF([Measure]=1,0,1)
var _2=CALCULATE(MAX('Table'[IDs]),FILTER(ALL('Table'),'Table'[IDs]=SELECTEDVALUE('Table'[IDs])&&'Table'[YEAR]=SELECTEDVALUE('Table (2)'[year])))
return 
IF([Measure]=1&&_2=BLANK(),"Y","N")

vpollymsft_0-1671674620982.png

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

 

Best Regards
Community Support Team _ Polly

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

View solution in original post

5 REPLIES 5
v-rongtiep-msft
Community Support
Community Support

Hi @BeaBF ,

Plaese refer to my pbix file to see if it helps you.

Create another table with year column. Then put the column into the slicer.

Then create  measures.

Measure = var _1=SELECTEDVALUE('Table (2)'[year])
var _1re=
IF(_1-1=SELECTEDVALUE('Table'[YEAR]),1,0)
return _1re
Measure 2 = var _1= IF([Measure]=1,0,1)
var _2=CALCULATE(MAX('Table'[IDs]),FILTER(ALL('Table'),'Table'[IDs]=SELECTEDVALUE('Table'[IDs])&&'Table'[YEAR]=SELECTEDVALUE('Table (2)'[year])))
return 
IF([Measure]=1&&_2=BLANK(),"Y","N")

vpollymsft_0-1671674620982.png

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

 

Best Regards
Community Support Team _ Polly

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

@v-rongtiep-msft 

It's perfect! I would have one more step, calculate the same Flag for the IDs which are present in the current year of selection (2022 for example) and which weren't there in the previous year (2021 if 2022 is selected). Could you help me with this new piece?

In the pbix, filtering on 2022, only ID 459356 should be flagged to Y, as it is not present in 2021. Filtering on 2021, instead, only ID 444521 should be set to Y, as it is not present in 2020.

Thank you very much,

BBF

Hi @BeaBF ,

Please refer to my pbix file.

Measure 3 = var _1=SELECTEDVALUE('Table (2)'[year])
var _1re=
IF(_1=SELECTEDVALUE('Table'[YEAR]),1,0)
return _1re
Measure 4 = var _1= IF([Measure 3]=1,0,1)
var _2=CALCULATE(MAX('Table'[IDs]),FILTER(ALL('Table'),'Table'[IDs]=SELECTEDVALUE('Table'[IDs])&&'Table'[YEAR]=SELECTEDVALUE('Table (2)'[year])))
var _1year=SELECTEDVALUE('Table (2)'[year])-1
var _1re=CALCULATE(MAX('Table'[YEAR]),FILTER(ALL('Table'),'Table'[YEAR]=_1year))
var _1jie=IF(MAX('Table'[YEAR])=_1re,_1re,BLANK())
var _111=MAXX(FILTER(ALL('Table'),'Table'[IDs]=SELECTEDVALUE('Table'[IDs])),_1jie)
return _111
//IF([Measure 3]=1&&_2=BLANK(),"Y","N")
Measure 5 = var _1=MAXX(FILTER(ALL('Table'),'Table'[IDs]=SELECTEDVALUE('Table'[IDs])),[Measure 4])
return
IF(_1=BLANK()&&[Measure 3]=1,"Y","N")

vpollymsft_0-1671701374811.pngvpollymsft_1-1671701383748.png

Best Regards
Community Support Team _ Polly

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

@v-rongtiep-msft I created these two measures:

Measure 3 = var _1=SELECTEDVALUE('Table (2)'[year])
var _1re=
IF(_1=SELECTEDVALUE('Table'[YEAR]),1,0)
return _1re
 
Measure 4 = var _1= IF([Measure 3]=1,0,1)
var _2=CALCULATE(MAX('Table'[IDs]),FILTER(ALL('Table'),'Table'[IDs]=SELECTEDVALUE('Table'[IDs])&&'Table'[YEAR]=SELECTEDVALUE('Table (2)'[year]) - 1))
return
IF([Measure 3]=1&&_2=BLANK(),"Y","N")
 
Do they look correct to you?
 
BBF

Hi @BeaBF ,

Yours seems a little simpler.

 

Best Regards
Community Support Team _ Polly

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.