Show Most Recent Price for Each Month

[pbeeyeqs]

I have 2 tables:

1) DATES Table

2) Price Changes Table

The price changes table has MODEL, BRAND, COMPANY, EFFECTIVE DATE and PRICE

I am trying to create something that shows the current price each month for each MODEL. If a MODEL didn't exist, then the price would be $0

Here is my sample data:

MODEL	BRAND	  COMPANY      EFFECTIVE DATE	 PRICE 
1	BRAND A	  COMPANY ONE	6/30/2018	 $0.75 
1	BRAND A	  COMPANY ONE	1/1/2019	 $0.85 
1	BRAND A	  COMPANY ONE	3/17/2019	 $1.05 
2	BRAND A	  COMPANY ONE	5/15/2018	 $3.00 
2	BRAND A	  COMPANY ONE	9/1/2018	 $2.00 
2	BRAND A	  COMPANY ONE	1/1/2019	 $4.00 
2	BRAND A	  COMPANY ONE	5/12/2019	 $4.50 
3	BRAND B	  COMPANY TWO	1/1/2017	 $6.00 
3	BRAND B	  COMPANY TWO	12/8/2017	 $8.00 
3	BRAND B	  COMPANY TWO	3/12/2019	 $9.00 

What I am expecting to see:

DATE	MODEL	BRAND	 COMPANY	 PRICE 
May-18	1	BRAND A	 COMPANY ONE	 $-   
Jun-18	1	BRAND A	 COMPANY ONE	 $0.75 
Jul-18	1	BRAND A	 COMPANY ONE	 $0.75 
Aug-18	1	BRAND A	 COMPANY ONE	 $0.75 
Sep-18	1	BRAND A	 COMPANY ONE	 $0.75 
Oct-18	1	BRAND A	 COMPANY ONE	 $0.75 
Nov-18	1	BRAND A	 COMPANY ONE	 $0.75 
Dec-18	1	BRAND A	 COMPANY ONE	 $0.75 
Jan-19	1	BRAND A	 COMPANY ONE	 $0.85 
Feb-19	1	BRAND A	 COMPANY ONE	 $0.85 
Mar-19	1	BRAND A	 COMPANY ONE	 $1.05 
Apr-19	1	BRAND A	 COMPANY ONE	 $1.05 
May-19	1	BRAND A	 COMPANY ONE	 $1.05 
Apr-18	2	BRAND A	 COMPANY ONE	 $-   
May-18	2	BRAND A	 COMPANY ONE	 $3.00 
Jun-18	2	BRAND A	 COMPANY ONE	 $3.00 
Jul-18	2	BRAND A	 COMPANY ONE	 $3.00 
Aug-18	2	BRAND A	 COMPANY ONE	 $3.00 
Sep-18	2	BRAND A	 COMPANY ONE	 $2.00 
Oct-18	2	BRAND A	 COMPANY ONE	 $2.00 
Nov-18	2	BRAND A	 COMPANY ONE	 $2.00 
Dec-18	2	BRAND A	 COMPANY ONE	 $2.00 
Jan-19	2	BRAND A	 COMPANY ONE	 $4.00 
Feb-19	2	BRAND A	 COMPANY ONE	 $4.00 
Mar-19	2	BRAND A	 COMPANY ONE	 $4.00 
Apr-19	2	BRAND A	 COMPANY ONE	 $4.00 
May-19	2	BRAND A	 COMPANY ONE	 $4.50 
Apr-18	3	BRAND B	 COMPANY TWO	 $8.00 
May-18	3	BRAND B	 COMPANY TWO	 $8.00 
Jun-18	3	BRAND B	 COMPANY TWO	 $8.00 
Jul-18	3	BRAND B	 COMPANY TWO	 $8.00 
Aug-18	3	BRAND B	 COMPANY TWO	 $8.00 
Sep-18	3	BRAND B	 COMPANY TWO	 $8.00 
Oct-18	3	BRAND B	 COMPANY TWO	 $8.00 
Nov-18	3	BRAND B	 COMPANY TWO	 $8.00 
Dec-18	3	BRAND B	 COMPANY TWO	 $8.00 
Jan-19	3	BRAND B	 COMPANY TWO	 $8.00 
Feb-19	3	BRAND B	 COMPANY TWO	 $8.00 
Mar-19	3	BRAND B	 COMPANY TWO	 $9.00 
Apr-19	3	BRAND B	 COMPANY TWO	 $9.00 
May-19	3	BRAND B	 COMPANY TWO	 $9.00 

Thanks

[OwenAuger · Accepted solution]

Hi @pbeeyeqs 

I would generally recommend a measure like this to return the most recent price at any point in time (sample pbix here😞

Effective Price = 
VAR MaxDate = MAX ( DATES[Date] )
RETURN
    CALCULATE ( 
        AVERAGE ( 'Price Changes'[PRICE] ),
        CALCULATETABLE ( 
            LASTDATE ( SUMMARIZE ( 'Price Changes', DATES[Date] ) ),
            DATES[Date] <= MaxDate
        )
    )

This measure essentially says: find the latest date appearing in the Price Changes table up to the max filtered DATES[Date], and return the average price on that date (which will be a single price if you are filtering on a Brand/Company/Model).

This measure works fine if you are filtered on a particular Brand/Company/Model which appears to be the granularity of prices in your dataset. If you need to handle cases where multiple Brand/Company/Model combinations need to be aggregated, you would need to extend the above measure.

Please post back if you need further help 🙂

Regards,

Owen

[pbeeyeqs]

This works perfectly. Thank you. Very much appreciated. 

I will be expanding on this report but will post as new Questions. 

Thanks again.