Forum Discussion

bubbledep's avatar
bubbledep
Icon for Helper I rankHelper I
1 year ago

Previous Non-Blank Quarter

Hi, need some help to get previous non-blank Quarter that are current filtered.     Like I show above, I'm getting the previous non-blank Quarter correctly, but the Qtr 2 value should be bla...
Need Help
Show and Tell
Tips and Tricks
bhanu_gautam's avatar
bhanu_gautam
Icon for Super User rankSuper User
1 year ago

bubbledep Try using

VAR CurrentQuarter = MAX(_dCalendar[Quarter])
VAR PreviousNonBlankQuarter =
CALCULATE(
MAX(_dCalendar[Quarter]),
FILTER(
ALLSELECTED(_dCalendar[Quarter]),
_dCalendar[Quarter] < CurrentQuarter &&
NOT(ISBLANK([YourMeasure]))
)
)
RETURN
IF(
ISBLANK(PreviousNonBlankQuarter),
BLANK(),
PreviousNonBlankQuarter
)

bubbledep's avatar
bubbledep
Icon for Helper I rankHelper I
1 year ago

Hi, didn't worked. It appears BLANK() to every Quarter, from here: 

IF(
ISBLANK(PreviousNonBlankQuarter),
BLANK(),
PreviousNonBlankQuarter
)

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi bubbledep 

    Thank you for reaching out to the Microsoft Fabric Forum Community.

    Add a  quarterkey column in your calendar table using below DAX

    QuarterKey = YEAR([Date]) * 10 + [QuarterNumber]

    Then use this DAX it may help you.

    PrevNonBlankQuarter :=
    VAR CurrentQKey = MAX(_dCalendar[QuarterKey])

    VAR HasEarlierQuarter =
    CALCULATE (
    COUNTROWS (
    FILTER (
    ALLSELECTED (_dCalendar),
    _dCalendar[QuarterKey] < CurrentQKey
    )
    )
    )

    VAR PrevNonBlankQKey =
    CALCULATE (
    MAX ( _dCalendar[QuarterKey] ),
    FILTER (
    ALLSELECTED (_dCalendar),
    _dCalendar[QuarterKey] < CurrentQKey &&
    NOT ISBLANK ( [YourMeasure] )
    )
    )

    RETURN
    IF (
    HasEarlierQuarter = 0,
    BLANK(),
    CALCULATE (
    SELECTEDVALUE ( _dCalendar[Quarter] ),
    _dCalendar[QuarterKey] = PrevNonBlankQKey
    )
    )




    If this information is helpful, please “Accept as solution”  to assist other community members in resolving similar issues more efficiently.
    Thank you.

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      Hi bubbledep 

      I hope the information provided was helpful. If you still have questions, please don't hesitate to reach out to the community.