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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors