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

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
Cyriackpazhe
Helper III
Helper III

firstnonblank

Screenshot (56).pngScreenshot (57).png

the intention is to obtain the firstnonblankvalue of the quarter. Why is the filter condition with parallelperiod necessary here??
Would it be okay if i remove the calculatetable as in the following code.

VAR FirstDateInQuarter =
FIRSTNONBLANK (
'Date'[Date],
COUNTROWS ( RELATEDTABLE( MSFT ) )
)
VAR Result =
CALCULATE (
AVERAGE ( MSFT[Value] ),
FirstDateInQuarter
)
RETURN
Result


1 ACCEPTED SOLUTION
OwenAuger
Super User
Super User

Hi @Cyriackpazhe 

In the original SOQ measure, PARALLELPERIOD creates a date filter by taking the set of visible dates from the original filter context and expanding those to complete calendar quarters spanning the minimum to maximum visible date.

 

The intention is to return the average value on the date returned by FIRSTNONBLANK within the quarter(s), regardless of the "location" of the current date filter within the quarter(s).

 

For example:

  • For any of the months January to March 2016, it returns all dates in Q1 2016.
  • For Q1 2016, it returns dates in Q1 2016.
  • For CY 2016, it returns Q1-Q4 2016 (i.e. the entire year).

In your suggested measure, without CALCULATETABLE/PARALLELPERIOD, FIRSTNONBLANK would operate within the existing filter context, and the result would be the first date on which the expression is nonblank within the existing filter context, which in this case would be the month, quarter, or year depending on the location in the matrix.

 

You can test out both measures in a visual and compare the difference.


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
LinkedIn

View solution in original post

3 REPLIES 3
v-veshwara-msft
Community Support
Community Support

Hi @Cyriackpazhe ,
Thanks for reaching out to Microsoft Fabric Community,
Just wanted to check if the solution provided by @OwenAuger has met your needs. If yes, Please consider marking it as "Accepted Solution" to assist others with similar queries. If further assistance is needed, please reach out.
Thank you.

Cyriackpazhe
Helper III
Helper III

Thanks

OwenAuger
Super User
Super User

Hi @Cyriackpazhe 

In the original SOQ measure, PARALLELPERIOD creates a date filter by taking the set of visible dates from the original filter context and expanding those to complete calendar quarters spanning the minimum to maximum visible date.

 

The intention is to return the average value on the date returned by FIRSTNONBLANK within the quarter(s), regardless of the "location" of the current date filter within the quarter(s).

 

For example:

  • For any of the months January to March 2016, it returns all dates in Q1 2016.
  • For Q1 2016, it returns dates in Q1 2016.
  • For CY 2016, it returns Q1-Q4 2016 (i.e. the entire year).

In your suggested measure, without CALCULATETABLE/PARALLELPERIOD, FIRSTNONBLANK would operate within the existing filter context, and the result would be the first date on which the expression is nonblank within the existing filter context, which in this case would be the month, quarter, or year depending on the location in the matrix.

 

You can test out both measures in a visual and compare the difference.


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
LinkedIn

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

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