Dynamic baseline and UOM

Hi danielfynes

I came up with this idea and just got around to posting it.

It could be overcomplicating things depending on your requirements....

Here's a link to my pbix.

1.  Set up the data model like this:
2. I have set up two date tables: Assessment Date and Baseline Date, following this method from SQLBI:
   https://www.sqlbi.com/articles/filtering-and-comparing-different-time-periods-with-power-bi/
3. The setup of the tables Transaction / UOM Distinct / UOM is designed so that:
   
   • The user selects the desired UOM using a slicer on UOM[UOM]
   • When the average transaction value is calculated, the relationships effectively allow a lookup from Transaction[UOM] to UOM[Conversion Factor], which will be filtered to the required value based on the UOM[UOM] filter.
   • This setup allows for mixed Transaction[UOM] values, e.g. you could have some rows with ltr and some rows with m3
4. The measures that need to be created as a result are:
   

   // This is the core calculation that averages Value over rows of Transaction table
   // in the selected UOM
   Average Final UOM = 
   AVERAGEX ( 
       'Transaction',
       'Transaction'[Value] * CALCULATE ( MIN ( UOM[Conversion Factor] ) )
   )
   
   // Assessment Period Average directly references Average Final UOM
   // Could merge this measure and previous measure into one
   Assessment Period Average = 
   [Average Final UOM]
   
   // Baseline Average performs the same calculation but using with
   // Assessment Date relationship activated
   Baseline Average = 
   CALCULATE (
       [Average Final UOM],
       ALL ( 'Assessment Date' ),
       USERELATIONSHIP ( 'Assessment Date'[Assessment Date], 'Baseline Date'[Baseline Date] )
   )
       

Then you can create a report page like this:

Regards

Owen