topic Display the different. in DAX Commands and Tips

Display the different.

matrix_user — Thu, 12 Jan 2023 00:15:47 GMT

Hello Community,

I need help with displaying a result that shows the different between two years for the same period. For example, the difference of sold items between 2022 Qrt 3 and 2021 Qrt 3.

Below is what I am trying to achieve ("Sold change" column of the "Power BI Matrix" table).

Somthing to note, is that my fact table contains only YYYY and Qrt# text data as dates of entries.

If anyone can help, I would be most grateful.

https://www.dropbox.com/scl/fo/y0dd0otjoblgsf1xtanf3/h?dl=0&rlkey=vq5hhz11r49633s2ub2o8na36

Re: Display the different.

adudani — Thu, 12 Jan 2023 01:11:59 GMT

Hi matrix_user ,

if you are able to add a date column (in the below code "Day Date" and create a relationship on this instead) in the fact table, this will enable time intelligence functions.

With Fact table[State] in matric values, you can create the following measures:

1. Current Sold = sum ( Fact table[Sold])

2. Current Remaining = sum( Fact table[Remaining])

3. SPLY Sold = Calculate( [Current Sold] , Dateadd( Date[Day Date]),-1,Year)
4. SPLY Remaining = Calculate( [Current Remaining] , Dateadd( Date[Day Date]),-1,Year)

5. Sold Change = [Current Sold] - [SPLY Sold]

This would solve the issue.

Appreciate a thumbs up if this is helpful.

Kindly accept this as the solution if your question is resolved.

Re: Display the different.

adudani — Thu, 12 Jan 2023 00:50:58 GMT

If you are unable to add a datecolumn in the fact table:

have a static filtered solution that achieves 4/5 columns :

https://1drv.ms/u/s!AkGuKJKxOlnAePGP2xw3i6zo7Ps?e=TT9lhl

Re: Display the different.

matrix_user — Thu, 12 Jan 2023 01:12:07 GMT

Hi Adudani,

I am unable to add a date column - so I appreciate your help. Thumbs up!