Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Qlik expression to dax

Guys, please help me in getting equivalent powerbi dax for this qlik view expression

 

IF(NUM(MONTH(DATE(vMaxDate,'MM')))='$1' AND CEIL(DAY(vMaxDate)/7)< MAX({$<MONTH={'$1/2019'}, PAYCYCLE_END_DATE=>}MaxNumOfPayWeeksInMonth),

SUM({$<MONTH = {'$1/2019'}, PAYCYCLE_END_DATE=, WeekNum={'<=2'}>}MID_MONTH_NET_AMOUNT), //then
SUM({$<MONTH = {'$1/2019'}, PAYCYCLE_END_DATE=>}MTD_NET_AMOUNT) //else
)

 

NUM function converts string text string to numerical in Qlik view.

MONTH function returns month number in qklik view.

CEIL rounds number to nearest integer.

 

I tried below Dax burt not working

IF(VALUE(MONTH(MaxDate)))="01" AND CEILING(DAY(MAXDATE/7),1)< CALCULATE(MAX(Query1[MaxNumOfPayWeeksInMonth]),FILTER(Query1, Query1[MONTH] = "01/2019")),
CALCULATE(SUM(Query1[MID_MONTH_NET_AMOUNT]), FILTER(Query1, Query1[MONTH] = "01/2019" && Query1[WeekNum] = "<=2")),
CALCULATE(SUM(Query1[MTD_NET_AMOUNT]), FILTER(Query1, Query1[MONTH] = "01/2019")))

 

Expecting a sooner solution

 

Need Help
  • v-eachen-msft's avatar
    v-eachen-msft
    6 years ago

    Hi Anonymous ,

     

    You could use the following DAX:

    IF (
    VALUE ( MONTH ( Query1[MaxDate] ) )
        = 1
            & CEILING ( DAY ( Query1[MAXDATE] / 7 ), 1 )
        < CALCULATE (
            MAX ( Query1[MaxNumOfPayWeeksInMonth] ),
            FILTER ( Query1, Query1[MONTH] = "01/2019" )
        ),
    CALCULATE (
        SUM ( Query1[MID_MONTH_NET_AMOUNT] ),
        FILTER ( Query1, Query1[MONTH] = "01/2019" && Query1[WeekNum] = "<=2" )
    ),
    CALCULATE (
        SUM ( Query1[MTD_NET_AMOUNT] ),
        FILTER ( Query1, Query1[MONTH] = "01/2019" )
    )
)

    1. VALUE() function returns a number, so it can not be compared with a text value like "01".

    2. AND cannot be used alone. You could use "&" or AND() function.

     

1 Reply

  • v-eachen-msft's avatar
    v-eachen-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Anonymous ,

     

    You could use the following DAX:

    IF (
    VALUE ( MONTH ( Query1[MaxDate] ) )
        = 1
            & CEILING ( DAY ( Query1[MAXDATE] / 7 ), 1 )
        < CALCULATE (
            MAX ( Query1[MaxNumOfPayWeeksInMonth] ),
            FILTER ( Query1, Query1[MONTH] = "01/2019" )
        ),
    CALCULATE (
        SUM ( Query1[MID_MONTH_NET_AMOUNT] ),
        FILTER ( Query1, Query1[MONTH] = "01/2019" && Query1[WeekNum] = "<=2" )
    ),
    CALCULATE (
        SUM ( Query1[MTD_NET_AMOUNT] ),
        FILTER ( Query1, Query1[MONTH] = "01/2019" )
    )
)

    1. VALUE() function returns a number, so it can not be compared with a text value like "01".

    2. AND cannot be used alone. You could use "&" or AND() function.