Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Graph using a measure which evaluates each date for previous dates

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:

CodeInvoiceDateUnit Price
066305/08/20221200
066317/08/20221300
213311/08/20222100
227312/08/20221300
165021/08/2022600
066303/09/20221500
213307/09/20222200

Answers would be:

DateCost
August5300
September5600

 

 

Need Help
  • v-jingzhang's avatar
    v-jingzhang
    3 years ago

    Hi Anonymous 

     

    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.

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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"
)

     

  • v-jingzhang's avatar
    v-jingzhang
    Community Support
    3 years ago

    Hi Anonymous 

     

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Thanks a bunch, that works