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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Anuj_1196
Frequent Visitor

Year on Year Difference

Hi @all,
Is it possible to create a measure to find variance in PowerBI like below?

Anuj_1196_0-1693882931396.png

 

6 REPLIES 6
Anuj_1196
Frequent Visitor

Hi Ashish,
Thanks for the response. Yes, the row labels are coming from the slicer and there would always be 2 dates in the row labels.

Hi,

Assuming you have a Calendar Table and the dates in the row labels are from th Calendar Table, try this DAX pattern

=calculate([Total],datesbetween(Calendar[date],MAXX(allselected(Calendar),calendar[date]),MAXX(allselected(Calendar),calendar[date]))-calculate([Total],datesbetween(Calendar[date],minx(allselected(Calendar),calendar[date]),minx(allselected(Calendar),calendar[date]))

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi Ashish,
I really appreciate your solution. Unfortunately, I'm experiencing an error: "A function 'DATESBETWEEN' has been used in a True/False expression that is used as a table filter expression. This is not allowed"

CALCULATE ( [Net Debt], DATESBETWEEN ( DimValdate[ValDate], MAXX ( ALLSELECTED ( DimValdate ), DimValdate[ValDate] ), MAXX ( ALLSELECTED ( DimValdate ), DimValdate[ValDate] ) ) - CALCULATE ( [Net Debt], DATESBETWEEN ( DimValdate[ValDate], MINX ( ALLSELECTED ( DimValdate ), DimValdate[ValDate] ), MINX ( ALLSELECTED ( DimValdate ), DimValdate[ValDate] ) ) ) )

You are welcome.  There should be another closing bracket before the - sign and remove one closing bracket from he end.

If it still does not work, then share the download link of the PBI file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi Ashish,
The modified formula did work but unfortunately didn't achieve what was actually expected.
Rows: ValDate (i.e Dec & June)
Columns: Fiscal Year (FY23, FY24,....)
Value: Net Debt(measure) , Net Debt Variance

Anuj_1196_0-1693974142752.png

 

Ashish_Mathur
Super User
Super User

Hi,

What would the result be if there were more than 2 dates in the row labels?  Also, are the row labels coming from a slicer selection?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.