Calculate difference in Qty according to date and hierarchy

[Zuzu]

Hello! I am new to Power BI and trying to wrap my head around different visualizations types and calculations for those. I am right now stuck with Hierarchy level calulations in a Matrix Viz.. If anyone can help me, that'd be great!! 

So I have a SQL table with Projects, Group, Unit, Code, Date, Quantity fields. I created a Hierarchy Project -> Group -> Unit -> Code. That is on my rows, Date is on Columns(I am right now only showing 2 weeks as columns- Prev Week, Current Week) and Quantity in Values. Everything works okay. The totals is shown correctly in each Hierarchy.

But now the request from Client is that they only want to see the "Type" where there is difference of Quantity values in the current and prev weeks. I am just flunking at it.. I don't know if I am going at it the right way or not. I tried to use ISINSCOPE() but could not get it right. 

I created a sample of the dataset in excel to show. This is the structure I have

Any help is appreciated! Thank you.     I am still trying to figuure out how to add the .pbix file.. 

EDIT : This is the link to the sample pbix : https://www.dropbox.com/s/xdq2bwsxabx50rk/ChangeOverWeeks.pbix?dl=0 

[amitchandak]

@Zuzu ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data. You can load to onedrive or dropbox and share.

In between refer one of the two can offer any help

https://www.sqlbi.com/articles/clever-hierarchy-handling-in-dax/

https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/

[Zuzu]

Also @amitchandak  I went through the blogs you shared. The using ISFILTERED gives me the same values as that I have currently. The only thing I'm looking for is to eliminate(not show) the rows where the Hierarchy level(1,2,6,7 etc...) are the same for both the dates

[Crystal_YW]

@Zuzu write a measure,  when prensentation  is in the Unit level and the result for the week to compare is the same, say, set a fixed amount, like 1

 and use the conditional formating, when the measure is 1, the font is white, then you could realize what you want

[Zuzu]

Hi @amitchandak   I created a sample pbix with dummy data but it has the same structure as my dataset. Let me know if you need it in a more detailed manner. Thank you for your help!

Link : https://www.dropbox.com/s/xdq2bwsxabx50rk/ChangeOverWeeks.pbix?dl=0

[Greg_Deckler]

@Zuzu - OK, found my difference between columns in a matrix formula. See Table (19) and Page 19 of attached PBIX. 

Note, you will probably not be able to attach a PBIX, you will need to share the PBIX via OneDrive or Box and share a link to it.

[Zuzu]

Hi! Thank you for your quick reply! I tried recreating using what you suggested (Working67 file with Table19).. Unfortunately, that is not working in my case as it's getting the Max of the Code value in a hierarchy and the totals is also changing.. I don't know if I putting it the right way or not.. 

I have added the file in Dropbox. Link - https://www.dropbox.com/s/xdq2bwsxabx50rk/ChangeOverWeeks.pbix?dl=0

ChangeOverWeeks is what I have currently and Table19 is what I recreated using your example. 

[Greg_Deckler]

@Zuzu - Man, I just wrote a difference between two date columns in a matrix like just recently, let me see if I can find it. But, you will most definitely need MM3TR&R - https://community.powerbi.com/t5/Quick-Measures-Gallery/Matrix-Measure-Total-Triple-Threat-Rock-amp-...