Calculate first value for months

Hello seinomozh ,

 

From what I could understand from the description shared, following is the recreation of your scenario:

 

Sample Data Table:

Date	System	Value
9-Jul-20	1st	9
10-Jul-20	1st	10
11-Jul-20	1st	11
12-Jul-20	1st	12
13-Jul-20	1st	13
14-Jul-20	1st	14
15-Jul-20	1st	15
16-Jul-20	1st	16
17-Jul-20	1st	17
18-Jul-20	1st	18
19-Jul-20	1st	19
20-Jul-20	1st	20
21-Jul-20	1st	21
22-Jul-20	1st	22
23-Jul-20	1st	23
24-Jul-20	1st	24
25-Jul-20	1st	25
26-Jul-20	1st	26
27-Jul-20	1st	27
28-Jul-20	1st	28
8-Jul-20	2nd	8
9-Jul-20	2nd	9
10-Jul-20	2nd	10
11-Jul-20	2nd	11
12-Jul-20	2nd	12
13-Jul-20	2nd	13
14-Jul-20	2nd	14
15-Jul-20	2nd	15
16-Jul-20	2nd	16
17-Jul-20	2nd	17
18-Jul-20	2nd	18
19-Jul-20	2nd	19
20-Jul-20	2nd	20
21-Jul-20	2nd	21
22-Jul-20	2nd	22
23-Jul-20	2nd	23
24-Jul-20	2nd	24
25-Jul-20	2nd	25
26-Jul-20	2nd	26
27-Jul-20	2nd	27
28-Jul-20	2nd	28
29-Jul-20	2nd	29
30-Jul-20	2nd	30
31-Jul-20	2nd	31
1-Aug-20	2nd	1
2-Aug-20	2nd	2
7-Aug-20	2nd	7
3-Aug-20	2nd	3
4-Aug-20	2nd	4
5-Aug-20	2nd	5
6-Aug-20	2nd	6
29-Jul-20	1st	29
30-Jul-20	1st	30
31-Jul-20	1st	31
1-Aug-20	1st	1
2-Aug-20	1st	2
3-Aug-20	1st	3
4-Aug-20	1st	4
5-Aug-20	1st	5
6-Aug-20	1st	6
7-Aug-20	1st	7

 

Measure:

First Value = 
VAR _MinDate = MIN('Table'[Date])
VAR _FirstValue = CALCULATE(FIRSTNONBLANK('Table'[Value],TRUE()),'Table'[Date] = _MinDate)
RETURN
_FirstValue

 

Result:

 

Hope this helps.

 

Cheers!
Vivek

If it helps, please mark it as a solution. Kudos would be a cherry on the top 🙂
If it doesn't, then please share a sample data along with the expected results (preferably an excel file and not an image)

Blog: vivran.in/my-blog
Connect on LinkedIn
Follow on Twitter

 

 

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)