Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Kazu
Helper II
Helper II

Value of the first month in a quarter or year

The source data is like below.

DateValue
Mar 20231
Apr 20231
May 20232
Jun 20232
Jul 20232

 

Using the data, I want to define a measure that shows the value of the first month, whether the values are shown by months, by quarter, or by year. 

For example, in case of shown by quarter, it would look like below. Q1 is empty because there is no data for Jan. Q2 is 1 which is from Apr. Q3 is 3 which is from Jul.

Q1Q2Q3
 12

LIkewise, when shown by year, it would look like below because the value for Jan is null (not exist).

2023
 

 

I was able to show Q2 and Q3 values correctly by using FIRSTNONBLANKVALUE() like below. (pleae ignore why I use MINX. it is for other reasons)

FIRSTNONBLANKVALUE = FIRSTNONBLANKVALUE(Dates[Date], MINX('Fact', 'Fact'[Value]))
However, this does not work for Q1 or year because they show 1 from the March data. How can I show blank here? I want to achieve this in a single measure and don't want to create a separate masure for monthly, quarterly and yearly.
1 ACCEPTED SOLUTION
Kazu
Helper II
Helper II

I was able to figure it out. Shareing the result hopeng it could help somebody else in future

 

Kazu_0-1685762901170.png

 

View solution in original post

1 REPLY 1
Kazu
Helper II
Helper II

I was able to figure it out. Shareing the result hopeng it could help somebody else in future

 

Kazu_0-1685762901170.png

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors