topic Re: PowerBI Percentage Difference between two selected dates in a filter in DAX Commands and Tips

PowerBI Percentage Difference between two selected dates in a filter

EuroK4238 — Thu, 29 Aug 2024 22:00:22 GMT

Hi,

I'm new to PowerBI and so far ChatGPT has been my best friend, but it seems it has exhausted its usefulness because we're going around in circles with the same proposed solutions.

what i am trying to make is a report with balance sheet and income statement data for several industry actors, for several quarters, and it's fine so far, but what i want to add now is a dynamic percentage change for these values, which picks only the most recent and the oldest selected dates in a filter.

So far, using this code i get a difference of zero.

The code used to select the dates is:

RecentDate = MAX('Balance Unpivot'[DATE])

AncientDate= MIN('Balance Unpivot'[DATE])

This works, it shows the correct dates in a card visual element.

then, the code used to pick the values for only these dates is as follows:

ValueAncientDate =

CALCULATE(

SELECTEDVALUE('Balance Unpivot'[Values]),

FILTER(

'Balance Unpivot',

'Balance Unpivot'[DATE] = [AncientDate]

)

ValueRecentDate =

CALCULATE(

SELECTEDVALUE('Balance Unpivot'[Values]),

FILTER(

'Balance Unpivot',

'Balance Unpivot'[DATE] = [RecentDate]

)

this is where we run into issues, because when i put these two measures into a matrix, both give me the same result, so calculating the difference between them always gives zero.

My data (unpivoted) is structured as follows:

Columns: Company, Date, ROW_ID, Attribute (total assets, liabilities, etc) and Values.

Please help!

Re: PowerBI Percentage Difference between two selected dates in a filter

ahadkarimi — Thu, 29 Aug 2024 23:14:01 GMT

Hi EuroK4238, try these measures below, and if you encounter any issues, let me know.

ValueAncientDate = CALCULATE( SUM('Balance Unpivot'[Values]), FILTER( 'Balance Unpivot', 'Balance Unpivot'[DATE] = [AncientDate] && 'Balance Unpivot'[Attribute] = SELECTEDVALUE('Balance Unpivot'[Attribute]) ) )

ValueRecentDate = CALCULATE( SUM('Balance Unpivot'[Values]), FILTER( 'Balance Unpivot', 'Balance Unpivot'[DATE] = [RecentDate] && 'Balance Unpivot'[Attribute] = SELECTEDVALUE('Balance Unpivot'[Attribute]) ) )

Calculate the percentage change:

PercentageChange = DIVIDE( [ValueRecentDate] - [ValueAncientDate], [ValueAncientDate], 0 )

Did I answer your question? If so, please mark my post as the solution! ✔️
Your Kudos are much appreciated! Proud to be a Solution Supplier!

Re: PowerBI Percentage Difference between two selected dates in a filter

EuroK4238 — Fri, 30 Aug 2024 14:39:02 GMT

Hi, thank you for your quick response.

I tried your proposed solution, but PerecentageChange comes up as zero for all attributes, same as the solutions proposed by ChatGPT.

Re: PowerBI Percentage Difference between two selected dates in a filter

monica345 — Tue, 03 Sep 2024 04:20:10 GMT

Hello,

EuroK4238 aarp mahjonggwrote:
Hi,

I'm new to PowerBI and so far ChatGPT has been my best friend, but it seems it has exhausted its usefulness because we're going around in circles with the same proposed solutions.

what i am trying to make is a report with balance sheet and income statement data for several industry actors, for several quarters, and it's fine so far, but what i want to add now is a dynamic percentage change for these values, which picks only the most recent and the oldest selected dates in a filter.

So far, using this code i get a difference of zero.

The code used to select the dates is:

RecentDate = MAX('Balance Unpivot'[DATE])
AncientDate= MIN('Balance Unpivot'[DATE])

This works, it shows the correct dates in a card visual element.

then, the code used to pick the values for only these dates is as follows:

ValueAncientDate =
CALCULATE(
SELECTEDVALUE('Balance Unpivot'[Values]),
FILTER(
'Balance Unpivot',
'Balance Unpivot'[DATE] = [AncientDate]
)
)

ValueRecentDate =
CALCULATE(
SELECTEDVALUE('Balance Unpivot'[Values]),
FILTER(
'Balance Unpivot',
'Balance Unpivot'[DATE] = [RecentDate]
)
)

this is where we run into issues, because when i put these two measures into a matrix, both give me the same result, so calculating the difference between them always gives zero.

My data (unpivoted) is structured as follows:
Columns: Company, Date, ROW_ID, Attribute (total assets, liabilities, etc) and Values.

Please help!

The issue with your current calculation lies in the use of SELECTEDVALUE. To correctly calculate the percentage change, use SUM to aggregate the values for the ancient and recent dates. Then, use DIVIDE to calculate the percentage change. Ensure that the Values column is numeric and that other filters don't interfere with the date-based calculations.

Re: PowerBI Percentage Difference between two selected dates in a filter

Anonymous — Mon, 02 Sep 2024 09:09:10 GMT

Thanks for the reply from ahadkarimi and monica345.

Hi EuroK4238 ,

Are you trying to do a percentage calculation on the values of the filtered recent date and ancient date in the matrix? If I understand you correctly, please see the steps below:

1.Create simple data:

2.Create a new table:

Date = VALUES('Balance Unpivot'[Date])

3.Use the Date column as the field of slicer:

4.The two original measures: RecentDate and AncientDate need to be modified.

AncientDate = MINX(VALUES('Date'[Date]),'Date'[Date]) RecentDate = MAXX(VALUES('Date'[Date]),'Date'[Date])

5.The final result is as follows:

Best Regards,
Zhu
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

Re: PowerBI Percentage Difference between two selected dates in a filter

EuroK4238 — Mon, 02 Sep 2024 13:01:03 GMT

Hello Anonymous and thank you for your response.

Would this work with data that isn't aggregated, and for multiple companies? here is my proposed report:

my rows contain specific balance sheet data, so i can't aggregate them. what i need to show is the percentage change for every element of the balance sheet, between two filtered dates, for several companies. The first matrix in the picture shows the balance sheet data for multiple companies and periods, and i intend to show the percentage change for every balance sheet element between the oldest and most recent date, that way only one column has to be added to the matrix, not one for every variation for every selected period.

the second matrix, on the right, is where i was testing the AncientDate and Recentdate values, but i hope the variation can be added as a single column beside the filtered dates for every selected company.

The final result you show in the 5th step would be perfect if it were filtered by balance sheet element.

Here's a sample of the Balance Unpivot table, for reference.

Empresa is the Companies, Fecha is Date, Atributo is the balance sheet elements, and Valor is the Value for that balance sheet element, for a specific date, for a specific company.