Forum Discussion

cyborgandy's avatar
cyborgandy
Helper II
2 years ago
Solved

Last Half Yearly Data

Hi All I have below table in my power bi dashboard, I want to write a dax to show the total out of term count of pervious year half yearly data on the basis of selected calendar period in the page, for example in the below snapshot month is selected as Feb-24 so it comes in H1 2024, so the new column should show out of term count  of H1 2023, similarly if someone selects Aug in the calendar period it should show the out of term count for H2 2023.

 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi cyborgandy 

     

    Please try this:

    First of all, I create a set of sample:

    Then add a new measure:

    MEASURE =
VAR _selectDate =
    MAX ( 'Table'[Date] )
VAR _currentCompany =
    MAX ( 'Table'[Company] )
VAR _Half =
    FILTER (
        ALL ( 'Table' ),
        MONTH ( 'Table'[Date] ) >= 1
            && MONTH ( 'Table'[Date] ) <= 6
            && YEAR ( 'Table'[Date] )
                = YEAR ( _selectDate ) - 1
            && 'Table'[Company] = _currentCompany
    )
VAR _Later =
    FILTER (
        ALL ( 'Table' ),
        MONTH ( 'Table'[Date] ) >= 7
            && MONTH ( 'Table'[Date] ) <= 12
            && YEAR ( 'Table'[Date] )
                = YEAR ( _selectDate ) - 1
            && 'Table'[Company] = _currentCompany
    )
RETURN
    IF (
        MONTH ( MAX ( 'Table'[Date] ) ) >= 1
            && MONTH ( MAX ( 'Table'[Date] ) ) <= 6,
        CALCULATE ( SUM ( 'Table'[Value] ), _Half ),
        CALCULATE ( SUM ( 'Table'[Value] ), _Later )
    )

    The result is as follow:

     

    Best Regards

    Zhengdong Xu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi cyborgandy 

     

    Please try this:

    First of all, I create a set of sample:

    Then add a new measure:

    MEASURE =
VAR _selectDate =
    MAX ( 'Table'[Date] )
VAR _currentCompany =
    MAX ( 'Table'[Company] )
VAR _Half =
    FILTER (
        ALL ( 'Table' ),
        MONTH ( 'Table'[Date] ) >= 1
            && MONTH ( 'Table'[Date] ) <= 6
            && YEAR ( 'Table'[Date] )
                = YEAR ( _selectDate ) - 1
            && 'Table'[Company] = _currentCompany
    )
VAR _Later =
    FILTER (
        ALL ( 'Table' ),
        MONTH ( 'Table'[Date] ) >= 7
            && MONTH ( 'Table'[Date] ) <= 12
            && YEAR ( 'Table'[Date] )
                = YEAR ( _selectDate ) - 1
            && 'Table'[Company] = _currentCompany
    )
RETURN
    IF (
        MONTH ( MAX ( 'Table'[Date] ) ) >= 1
            && MONTH ( MAX ( 'Table'[Date] ) ) <= 6,
        CALCULATE ( SUM ( 'Table'[Value] ), _Half ),
        CALCULATE ( SUM ( 'Table'[Value] ), _Later )
    )

    The result is as follow:

     

    Best Regards

    Zhengdong Xu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • cyborgandy's avatar
      cyborgandy
      Helper II
      2 years ago

      Hi could you please share the working pbi file to understand more about this DAX 🙂

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi cyborgandy 

         

        Blow is the pbix.

         

        Best Regards

        Zhengdong Xu
        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.