Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
seinomozh
Frequent Visitor

Calculate first value for months

‎08-26-2020 04:00 PM
 
Hi guys, I would like to create a measure that calculates first value for each month based on filter.

 

seinomozh_1-1598482767169.png

 

 

This is an example:

DateSystemValue
09.07.20201st266471,4
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
08.07.20202nd263707,1
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,86
02.08.20202nd86292,86
07.08.20202nd86292,86
03.08.20202nd86292,86
04.08.20202nd86292,86
05.08.20202nd86292,86
06.08.20202nd86292,86
29.07.20201st266471,4
30.07.20201st266471,4
31.07.20201st266471,4
01.08.20201st83528,57
02.08.20201st83528,57
03.08.20201st83528,57
04.08.20201st83528,57
05.08.20201st83528,57
06.08.20201st83528,57
07.08.20201st

83528,57

 

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

 

If i use slicer on a date for ex only july it should show:

1st system - july 266471,4286

2nd system - july 86292,85714

 

I think it should calculate first value for selected month. Thank you for any help.

Labels:
Message 1 of 8
1,125 Views
0
7 REPLIES 7
FrankAT
Community Champion
Community Champion

‎08-28-2020 08:53 AM

Hi @seinomozh 

I found the following solution:

 

28-08-_2020_17-51-40.png

 

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)

Message 6 of 8
1,082 Views
0
seinomozh
Frequent Visitor
In response to FrankAT

‎08-28-2020 09:47 AM

@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. 

Message 7 of 8
1,080 Views
0
v-shex-msft
Community Support
Community Support
In response to seinomozh

‎08-30-2020 07:35 PM

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Message 8 of 8
1,050 Views
0
vivran22
Community Champion
Community Champion

‎08-27-2020 08:20 AM

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:

image.png

 

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

 

 
 

 

Message 2 of 8
1,120 Views
0
seinomozh
Frequent Visitor
In response to vivran22

‎08-28-2020 02:32 AM

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.

 

seinomozh_0-1598606614784.png

 

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)

Message 3 of 8
1,100 Views
0
vivran22
Community Champion
Community Champion
In response to seinomozh

‎08-28-2020 07:33 AM

@seinomozh 

 

I did not understand the logic of 350k? How did you arrive at this value?

 

Cheers!
Vivek

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

Message 4 of 8
1,091 Views
0
seinomozh
Frequent Visitor
In response to vivran22

‎08-28-2020 08:33 AM

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

Message 5 of 8
1,088 Views
0

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

View All