Graph using a measure which evaluates each date for previous dates

[IssieWissie]

Hi All,

I have a measure which works for me in a card format with a relative date filter. However, I'm struggling to get this on a graph now. My data is purchasing transactions which have a unit price and serial code associated with them. Purchases can be months in advance. So whenever I want to evaluate a price of a part for this month, the last transaction could have been 3 months ago:

1. Measure finding the last transaction

Average = CALCULATE(AVERAGE(Purchasing2[Unit Price]),'Calendar Table'[Date]=MAX(Purchasing2[InvoiceDate]))

2. Measure evaluating the sum of all the serial codes

Cost = SUMX(SUMMARIZE(Purchasing2,Purchasing2[Code],"Price",[Average]),[Price])

With a couple filters I can get the correct card:

But now I'd like to make a graph of this that evaluates each month. A sample:

Code	InvoiceDate	Unit Price
0663	05/08/2022	1200
0663	17/08/2022	1300
2133	11/08/2022	2100
2273	12/08/2022	1300
1650	21/08/2022	600
0663	03/09/2022	1500
2133	07/09/2022	2200

Answers would be:

Date	Cost
August	5300
September	5600

[v-jingzhang · Accepted solution]

Hi @IssieWissie 

Here is my solution with a measure. Firstly I add a Dim Code table to the model which has all distinct code values from Purchasing2. Relate it to Purchasing2 on Code columns. 

Then use the following measure. 

Measure2 = 
VAR endDate =
    ENDOFMONTH ( 'Calendar Table'[Date] )
VAR startDate =
    EDATE ( STARTOFMONTH ( 'Calendar Table'[Date] ), -12 )
VAR table1 =
    SUMMARIZE (
        'Code Table',
        'Code Table'[Code],
        "price",
            AVERAGEX (
                TOPN (
                    1,
                    FILTER (
                        ALL ( Purchasing2 ),
                        Purchasing2[Code] = 'Code Table'[Code]
                            && Purchasing2[InvoiceDate] >= startDate
                            && Purchasing2[InvoiceDate] <= endDate
                    ),
                    [InvoiceDate], DESC
                ),
                [Unit Price]
            )
    )
RETURN
    SUMX ( table1, [price] )

Hope this is helpful. Sample file has been attached at bottom.

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[IssieWissie]

Thanks a bunch, that works 

[IssieWissie]

Quick update for anyone that has a similar situation. I have been able to get what I need, it unfortunately had to be a calculated column. If anyone still has a suggestion for a measure with the same product I'd appreciate it:

Average = CALCULATE(AVERAGE(Purchasing2[Price GBP]),'Calendar Table'[Date]=MAX(Purchasing2[InvoiceCreditDate]))

Cost = 
CALCULATE (
    SUMX (
        SUMMARIZE ( Purchasing2, Purchasing2[Code], "price", [Average] ),
        [price]
    ),
    DATESBETWEEN (
        'Calendar Table'[Date],
        DATE ( YEAR ( 'Calendar Table'[Date] ) - 1, MONTH ( 'Calendar Table'[Date] ), DAY ( 'Calendar Table'[Date] ) ),
        'Calendar Table'[Date]
    ),
    Purchasing2[Code] = "0663"
        || Purchasing2[Code] = "2133"
        || Purchasing2[Code] = "2273"
        || Purchasing2[Code] = "0861"
        || Purchasing2[Code] = "1650"
        || Purchasing2[Code] = "3727"
        || Purchasing2[Code] = "1980"
        || Purchasing2[Code] = "1349"
        || Purchasing2[Code] = "1200"
        || Purchasing2[Code] = "0893"
        || Purchasing2[Code] = "2670"
)