Forum Discussion

PBInewbee123's avatar
PBInewbee123
Frequent Visitor
3 years ago
Solved

Show last data from month

Hi,

I need some help 🙂

I have a dataset which shows me the stock value per week (situation picture we take every Sunday). With this an evolution chart is created. However, now we also want to see the evolution per month. For this, I want to use the last value of the month (iso average or sum)

Here below an example of the data that I have

DateValue

2022-09-04

1000
2022-09-111200
2022-09-181100
2022-09-25900
2022-10-02950
2022-10-091100
2022-10-231100
2022-10-301000

 

Result that I want to end up wihth:

MonthValue
2022-09900
2022-101000

 

Thank you!

Need Help
  • v-easonf-msft's avatar
    v-easonf-msft
    3 years ago

    Hi, PBInewbee123 

    You can add a calendar table with a column 'YearMonth'.

    Then try measure like:

    M_VALUE = 
VAR _lastdate =
    CALCULATE (
        LASTDATE ( 'Table'[Date] ),
        ALLEXCEPT ( 'Calendar', 'Calendar'[YearMonth] )
    )
RETURN
    CALCULATE (
        MAX ( 'Table'[Value] ),
        FILTER ( 'Table', 'Table'[Date] = _lastdate )
    )

     

    Best Regards,
    Community Support Team _ Eason

2 Replies

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    3 years ago

    Hello,

     

    Try the following code:

    Last Value = MAXX(TOPN(1, 'Table', 'Table'[Date],DESC), 'Table'[Value])
  • v-easonf-msft's avatar
    v-easonf-msft
    Icon for Community Support rankCommunity Support
    3 years ago

    Hi, PBInewbee123 

    You can add a calendar table with a column 'YearMonth'.

    Then try measure like:

    M_VALUE = 
VAR _lastdate =
    CALCULATE (
        LASTDATE ( 'Table'[Date] ),
        ALLEXCEPT ( 'Calendar', 'Calendar'[YearMonth] )
    )
RETURN
    CALCULATE (
        MAX ( 'Table'[Value] ),
        FILTER ( 'Table', 'Table'[Date] = _lastdate )
    )

     

    Best Regards,
    Community Support Team _ Eason