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
vivran22's avatar
vivran22
Community Champion
6 years ago

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

 

 
 

 

seinomozh's avatar
seinomozh
Frequent Visitor
6 years ago

Hi vivran22 

 

Not exactly. It calculates only first value for each system without date filter. E.g. for july only OR july and aug - same result. 

Should be 350K for each row without filter. Below result works only for july dates.

 

 

Best guess - to create new column that gives "1" for first day per month for each system. Like:

 

DateSystemValueNew column
09.07.20201st266471,41
10.07.20201st266471,4 
11.07.20201st266471,4 
12.07.20201st266471,4 
13.07.20201st266471,4 
14.07.20201st266471,4 
15.07.20201st266471,4 
16.07.20201st266471,4 
17.07.20201st266471,4 
18.07.20201st266471,4 
19.07.20201st266471,4 
20.07.20201st266471,4 
21.07.20201st266471,4 
22.07.20201st266471,4 
23.07.20201st266471,4 
24.07.20201st266471,4 
25.07.20201st266471,4 
26.07.20201st266471,4 
27.07.20201st266471,4 
28.07.20201st266471,4 
29.07.20201st266471,4 
30.07.20201st266471,4 
31.07.20201st266471,4 
01.08.20201st83528,571
02.08.20201st83528,57 
03.08.20201st83528,57 
04.08.20201st83528,57 
05.08.20201st83528,57 
06.08.20201st83528,57 
07.08.20201st83528,57 
08.07.20202nd263707,11
09.07.20202nd263707,1 
10.07.20202nd263707,1 
11.07.20202nd263707,1 
12.07.20202nd263707,1 
13.07.20202nd263707,1 
14.07.20202nd263707,1 
15.07.20202nd263707,1 
16.07.20202nd263707,1 
17.07.20202nd263707,1 
18.07.20202nd263707,1 
19.07.20202nd263707,1 
20.07.20202nd263707,1 
21.07.20202nd263707,1 
22.07.20202nd263707,1 
23.07.20202nd263707,1 
24.07.20202nd263707,1 
25.07.20202nd263707,1 
26.07.20202nd263707,1 
27.07.20202nd263707,1 
28.07.20202nd263707,1 
29.07.20202nd263707,1 
30.07.20202nd263707,1 
31.07.20202nd263707,1 
01.08.20202nd86292,861
02.08.20202nd86292,86 
03.08.20202nd86292,86 
04.08.20202nd86292,86 
05.08.20202nd86292,86 
06.08.20202nd86292,86 
07.08.20202nd86292,86 

 

Than easiest way - CALCULATE(SUM('Table'[Value]),'Table'[New_Column]=1)

    • seinomozh's avatar
      seinomozh
      Frequent Visitor
      6 years ago

      Hi vivran22 

       

      I explained this in my first post 🙂

       

      Expected result without filters:

      1st350000
      2nd350000

       

      That means that for each system with have 2 months 7 and 8 and for each month we get first value. E.g.

      1st system - july 266471,4286 and august 83528,57143

      2nd system - july 86292,85714 and august 263707,1429