Forum Discussion
firstnonblank
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
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.
3 Replies
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.
Thanks
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.
