How can I assign Single value to a variable dynamically?

[aforapple_27]

Amount for Previous Year =

var prevYear = Calculate(SELECTEDVALUE('Raw data'[Year])-1)

var prevYearFinal = prevYear

return CALCULATE(SUM('Raw data'[Amount]), 'Raw data'[Year] IN {2024})  

Above code gives me expected results. But when I select 2025 in slicer, the below code is not giving me expected results at all. It shows correct values, but in some places it just shows blank, but the total at end is correct. 

Amount for Previous Year =

var prevYear = Calculate(SELECTEDVALUE('Raw data'[Year])-1)

var prevYearFinal = prevYear

return CALCULATE(SUM('Raw data'[Amount]), 'Raw data'[Year] IN {prevYearFinal})  

[v-tsaipranay]

Hi @aforapple_27 ,

Could you please confirm whether you have resolved issue. If yes, you are welcome to share your workaround and mark it as a solution so that other users can benefit as well. This will be helpful for other community members who have similar problems to solve it faster. 

If we don’t hear back, we’ll go ahead and close this thread.Should you need further assistance in the future, we encourage you to reach out via the Microsoft Fabric Community Forum and create a new thread. We’ll be happy to help.

Thank you.

[v-tsaipranay]

Hi @aforapple_27 ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[aforapple_27]

Nope this issue is still not fixed. I would need to make a dummy data i guess so that you guys could work through it. Just haven't been able to wrap my time around it

[v-tsaipranay]

Hello @aforapple_27 ,

Thank you for the update. When you have a moment, please share a dummy dataset or a simplified version of the model. This will help me reproduce the issue more accurately and assist you better.

Thank you.

[Ashish_Mathur]

Hi,

Assuming you already have a date column in the fat table (and if you do not have it, you will create it), try the following approach

1. Create a Calendar table with a calculated column to extract the Year
2. Create a relationship (Many to One and Single) from the Date column of the Fact table to the Date column of the Calendar table
3. To your slicer, drag Year from the Calendar table and select a Year there
4. Write these measures

Total = SUM('Raw data'[Amount])

Total in PY = calculate([Total],previousyear(calendar[date]))

Hope this helps.

[aforapple_27]

hi @v-tsaipranay , @hnguy71 

I tried both of your code but unfortunately it didn't work as expected. My table is doing comparision between sales data of 2025 and 2024 on month by month basis. The thing is both with my code and your code, it shows up blank whereever selected year in slicer has no transaction. For example, if there was no transaction in March, 2025, It comes up blank for that month transaction ( which is expected) but it also comes up blank for 2024, ( there is transaction in March 2024).

Please let me know if you guys need any more information to support me through this.

[v-tsaipranay]

Hi @aforapple_27 ,

I see what’s going wrong now thanks for the clarification.

The issue happens because when a month has no data in 2025, the context carries over and blocks the 2024 data from showing too.

I’ve updated the measure to fix this by ensuring the 2024 data still appears even if 2025 is blank:

Amount for Previous Year =

VAR prevYear = SELECTEDVALUE('Raw data'[Year]) - 1

RETURN

    CALCULATE(

        SUM('Raw data'[Amount]),

        TREATAS(

            VALUES('Raw data'[Month]),

            'Raw data'[Month]

        ),

        'Raw data'[Year] = prevYear

    )

I hope this will reslove your issue, if you need any further assistance, feel free to reach out.

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

Thankyou.

[v-tsaipranay]

Hi @aforapple_27 ,

I wanted to check if you had the opportunity to review the information provided and also thank you @Ashish_Mathur for your inputs. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[hnguy71]

Hi @aforapple_27 

Do you mind sharing some sample data and expected outputs?

[v-tsaipranay]

Hello @aforapple_27 ,

Could you please sharing some sample data and expected outputs, so that it could be easy for understand and give you best possible solution to resolve your issue.

Thank you.

[v-tsaipranay]

Hi @aforapple_27 ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-tsaipranay]

Hi @,

Thank you for using Microsoft Community Forum. Also thankyou @hnguy71  for your inputs.

The problem is due to context behavior in Power BI:

• SELECTEDVALUE() works in slicer context, but when the measure evaluates row-by-row (like in a table), that slicer context doesn’t hold the same way.
• Totals work because the context aggregates the whole data set — but rows go blank because the row itself doesn’t "see" the slicer’s value properly.

Try this measure, I have tried with some sample data in my end it worked fine:

Amount for Previous Year =

VAR prevYearFinal = SELECTEDVALUE('Raw data'[Year]) - 1

RETURN

    CALCULATE(

        SUM('Raw data'[Amount]),

        'Raw data'[Year] = prevYearFinal

    )

I am also including pbix file for your better understanding, please have a look into it.

I hope this will reslove your issue, if you need any further assistance, feel free to reach out.

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

Thankyou.

[aforapple_27]

Hello,

      I tried your code but still it didn't work unfortunately in my case.

[hnguy71]

Hi @aforapple_27 

Try this instead:

Amount for Previous Year =
var currYear = SELECTEDVALUE('Raw data'[Year])
var prevYear = currYear - 1
return 
CALCULATE(SUM('Raw data'[Amount]), 'Raw data'[Year] = prevYear, ALL('Raw data'))  

This should return you the amount for previous year.