Conditional formatting based on Conversion measure - previous year

[Anonymous]

Hi,

I think I have got close to what I need in this solution...

https://community.powerbi.com/t5/Desktop/Conditional-formatting-based-on-another-table/m-p/942131

...but not quite there and think it's related to the fact that my year format is in text not date, but would be stuck on the right DAX.

Table currently looks like this...

I'd like to display any increases in percentages from the previous year in green, decreases in red, obviously relating to each male/female category ie. increase/decrease in that gender only. I'm presuming I could create a date table and create a relationship for 17/18 for example to 01/01/2017, but would be stuck on the DAX.

Any suggestions?

[daxer-almighty]

Hiya

Can you share a file with data? I wouldn't like to create it manually myself. You can obfuscate the pieces you don't want others to see. Sharing here means "place a link to the file in here." The file can be stored on Google Drive, OneDrive or any file sharing service we'll be able to access.

[Anonymous]

@daxer-almighty 

https://drive.google.com/file/d/1uLo6rXEQnwJGpulmJNPzo0oWI4xamUbk/view?usp=sharing

Hopefully you should be able to access a version of the .pbix here

[Anonymous]

@daxer-almighty 

Thank you for this. Managed to get my date table sorted so that my new matrix matches the values that I used in the previous matrix. Have searched high and low here to find relevant dax that shows decrease (red) and increase (green) conditional formatting for changes in percentages relating individually to two criteria, in this case Male/Female. I looked at the time related link you sent but these seem related to creating the calculation, rather then conditional formatting.

Any suggestions anyone to point me in the right direction of a solution on here?

Thanks

[daxer-almighty]

@Anonymous 

To be able to do time calculations using the standard time-intel functions (and it looks like this could be what you need) you have to create a Date table and relate it to the fact table. The Date table must be created the way that, for instance, this article describes: https://dax.guide/dateadd

Once you have such a table, you can create a measure that will return ±1 depending on the increase/decrease in the measure from one period to another. Since you're using Academic Years the table will have to feature a column that will store consecutive numbers for these years so that you are able to move forward and back within these years in the DAX code. If you want to see how to calculate deltas between the current and the prior period, you can read this: 

Custom time-related calculations – DAX Patterns