Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 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 ...
Need Help
  • v-jingzhang's avatar
    v-jingzhang
    4 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
4 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"
)