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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Difference between SELECTED/subset of Date columns in a Matrix table, compare years

Hi All,

 

Background:  So this question, is very popular on as PowerBi's current design, doesn't work well with Matries.  Following links to similar topics.

 

https://community.powerbi.com/t5/Desktop/Calculate-the-difference-between-two-columns-without-hard-c...

https://community.powerbi.com/t5/Desktop/Difference-between-2-columns-in-a-Matrix-Table/m-p/81614#M3...

https://community.powerbi.com/t5/Desktop/Measure-to-Calculate-Difference-vs-Previous-Period/m-p/5605...

 

the last link is particularly useful as I manage to get the code working.

 

image.png

One of the issue is the order of months and order of comparison. Thus I created a new column with 072017, 072018 to order them for comparison and updated the code. unfortunately this did not work.

 

Alternatively, since the months are a year apart, some kind of last year function could work.   Which lead me to this link.

https://community.powerbi.com/t5/Desktop/Comparing-this-year-and-last-year-measure-on-a-bar-chart/td...

 

I got this code working, but having difficulty getting it to filter, to include a row catagory:

 

image.png

 

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

See if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

6 REPLIES 6
Greg_Deckler
Super User
Super User

See if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

hi @Greg_Deckler  could you provide some help here, i want to replicate the below excel in power bi , the table A:D was created with pivot and 'final status' has been obtained with IF condition(conditions highlighted in formula tab). In power BI,

a. I want to provide the option to user to select any YEARMO and basis selection, it should compute the final status. for eg. if i choose YEARMO as 201907 it will use 201908,201909 and 201910 data and compare.

b. Also treat null values/blank entries as 0 and use in comparison 

I have already created pivot by grouping ( by yearmo and org code), how do i provide the functionality of dynamic selection and then comparison/computation basis input. I refer to the Time intelligence created by you where we can select the year and the month and the data is computed accordingly. abc.PNGexceloutput.PNG

Anonymous
Not applicable

Hi Greg,

 

Would there be away to adapt your TITHW method but for catagoried. I.e. instead of caculating YoY %/diff you used it to calculate %/Diff between two catagories.

 

I am trying to solve slicing issues on %/diff calculations between catagories. 

 

https://community.powerbi.com/t5/Desktop/visual-slicer-and-calculate-filter-on-the-same-column/m-p/5...

 

Regards,

 

Aaron

 

Edit: Spelling.

Anonymous
Not applicable

Hi @Greg_Deckler Thank you for this, it worked.

 

However, it is very messy and resource intensive.  Each column, 2017, 2018, % change and Diff. Had to be it's own measure in order for all of them to be in the same table.

 

image.png

 

Here is the code from THE HARD WAY with adaptions:

 

% Change = 
VAR __MaxYear = MAX('Winter Analysis'[Snap Year])
VAR __MaxMonth = MAX('Winter Analysis'[Snap Month No])
VAR __TmpTable = CALCULATETABLE('Winter Analysis',ALL('Winter Analysis'[Snap Year]),All('Winter Analysis'[Snap Month Name]))
VAR __currentYear = AVERAGEX(FILTER(__TmpTable,[Snap Year]=__MaxYear && [Snap Month No] <= __MaxMonth),[DailyRate_avg])
VAR __previousYear = AVERAGEX(FILTER(__TmpTable,[Snap Year]=__MaxYear - 1 && [Snap Month No] <= __MaxMonth),[DailyRate_avg])
RETURN DIVIDE(__currentYear - __previousYear,__previousYear,0)

Since all four measures had the same variables (I just changed the last 3 lines). I tried taking the variables out as seperate measures and a seperate table.  Also since the __Temp table seems to be the same as the Winter Analysis table, I tried removing that too. But the numbers that these processes were returning were different.

 

So I will use this for now. Thanks! But if there is a more elegant method, or less resource hungry, that would be great. 🙂

Can you post some sample source data for me in a format that I can copy and paste? I can take a look at optimizing it.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

Here is the basic Data, (Anonymized) with out dates being broken down into the various parts on the same table. Let me know if this is not sutables.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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