Forum Discussion

seinomozh's avatar
seinomozh
Frequent Visitor
6 years ago

Calculate first value for months

  Hi guys, I would like to create a measure that calculates first value for each month based on filter.       This is an example: Date System Value 09.07.2020 1st 266471,4 ...
Need Help
FrankAT's avatar
FrankAT
Community Champion
6 years ago

Hi seinomozh 

I found the following solution:

 

 

First Value of Month = 
CALCULATE(
    MIN('Table'[Value]),
    FILTER(
        ALLEXCEPT('Table','Table'[System]),
        MIN('Table'[Date])='Table'[Date]
    )
)

 

With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)

seinomozh's avatar
seinomozh
Frequent Visitor
6 years ago

FrankAT 

 

This is great, but it is not summarize if remove month. Idea is to create a measure that can show you proper value per choosen month. So if i choose 2 month (jul+aug) it should be 350K per system. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi seinomozh,

    You can try to use the below formula to use system category and date filter to find out the minimum date and correspond value:

     

    Measure =
VAR minDate =
    CALCULATE (
        MIN ( 'Table'[Date] ),
        ALLSELECTED ( 'Table' ),
        VALUES ( 'Table'[Date] ),
        VALUES ( 'Table'[System] )
    )
RETURN
    CALCULATE (
        MIN ( 'Table'[Value] ),
        FILTER ( ALLSELECTED ( 'Table' ), [Date] = minDate ),
        VALUES ( 'Table'[System] )
    )

     

    Regards,

    Xiaoxin Sheng