Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Calculation per Quarter and month

Hi All,

 

I need your help to solve the below query, please help me to solve this issue.

 

Condition: A>=1/Month and B>=6 / Quarter and C>=1/Quarter then the result should be pass else fail.

Jan  Feb  Mar  Result
ABCABCABC 
14112 13 Pass
16  2 15 Fail
11 12 131Pass
16 24 15 Fail

Thank you in advance

 

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Anonymous ,

    I created a sample pbix file(see attachment) for you, please check whether that is what you want. You can create a calculated column as below:

    Result = 
VAR _abymonth =
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Type] = EARLIER ( 'Table'[Type] )
                && 'Table'[Item] = "A"
                && MONTH ( 'Table'[Date] ) = EARLIER ( 'Table'[Date].[MonthNo] )
        )
    )
VAR _bbyquarter =
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Type] = EARLIER ( 'Table'[Type] )
                && 'Table'[Item] = "B"
                && QUARTER ( 'Table'[Date] ) = EARLIER ( 'Table'[Date].[QuarterNo] )
        )
    )
VAR _cbyquarter =
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Type] = EARLIER ( 'Table'[Type] )
                && 'Table'[Item] = "C"
                && QUARTER ( 'Table'[Date] ) = EARLIER ( 'Table'[Date].[QuarterNo] )
        )
    )
RETURN
    IF ( _abymonth >= 1 && _bbyquarter >= 6 && _cbyquarter >= 1, "Pass", "Fail" )

    Best Regards

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Anonymous ,

    I created a sample pbix file(see attachment) for you, please check whether that is what you want. You can create a calculated column as below:

    Result = 
VAR _abymonth =
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Type] = EARLIER ( 'Table'[Type] )
                && 'Table'[Item] = "A"
                && MONTH ( 'Table'[Date] ) = EARLIER ( 'Table'[Date].[MonthNo] )
        )
    )
VAR _bbyquarter =
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Type] = EARLIER ( 'Table'[Type] )
                && 'Table'[Item] = "B"
                && QUARTER ( 'Table'[Date] ) = EARLIER ( 'Table'[Date].[QuarterNo] )
        )
    )
VAR _cbyquarter =
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Type] = EARLIER ( 'Table'[Type] )
                && 'Table'[Item] = "C"
                && QUARTER ( 'Table'[Date] ) = EARLIER ( 'Table'[Date].[QuarterNo] )
        )
    )
RETURN
    IF ( _abymonth >= 1 && _bbyquarter >= 6 && _cbyquarter >= 1, "Pass", "Fail" )

    Best Regards