Forum Discussion

zahlenschubser's avatar
zahlenschubser
Icon for Helper IV rankHelper IV
2 years ago
Solved

Potential bug with date() ?

I have a very curious issue with a switch function containing a lot of date() formulas. What I am trying to do is get a column that shows me when one of our invoicing groups will be billed next, mos...
Need Help
  • zahlenschubser's avatar
    zahlenschubser
    2 years ago

    I have the weirdest fix for this ... switch back the regional settings in PBI to the default.

     

    A little annoying, but trying to figure out and fix what PBI messes up in the background is way worse.

     

MFelix's avatar
MFelix
Icon for Super User rankSuper User
2 years ago

Hi zahlenschubser ,

 

Be carefull that the SWITCH function is done based on the order of the several rows so if the first one is getting a true value the statetment stops, in this case not sure what is group 6 you refer but please check the order of your switch statement because it can be based on that.

zahlenschubser's avatar
zahlenschubser
Icon for Helper IV rankHelper IV
2 years ago

The conditions being triggered one after another is fine, as I tried to make them specific for each customer group.

What I intended to do is have switch() evaluate a combination of the customer group code and the current month and then determine when the group will next be up for billing.

For example
, rechnungsgruppe[rechnungsgruppe_pk] IN {43 , 45 , 48} && MTH in {1,2,3}, DATE(YR , 3 , 1)
supposedly means that if the group is 43 or 45 or 48 AND the current month is Jan/Feb/Mar > output March of the current year as next billing.

 

I tried to streamline and reorder the code a bit so I don't have multiple lines trigger the same date, but I'm still getting the same error. =(

REG_nextbill = VAR YR = YEAR(today() - 5)
               VAR MTH = MONTH(today() - 5)
               RETURN
        switch(TRUE()
            , rechnungsgruppe[rechnungsgruppe_pk] IN {43 , 45 , 48} && MTH in {1,2,3}, DATE(YR , 3 , 1)
            , rechnungsgruppe[rechnungsgruppe_pk] IN {43 , 45 , 48} && MTH in {4,5,6}
                || rechnungsgruppe[rechnungsgruppe_pk] = 9 && MTH in {1,2,3,4,5,6}
                    , DATE(YR , 6 , 1)
            , rechnungsgruppe[rechnungsgruppe_pk] IN {43 , 45 , 48} && MTH in {7,8,9}, DATE(YR , 9 , 1)
            , rechnungsgruppe[rechnungsgruppe_pk] IN {43 , 45 , 48} && MTH in {10,11,12}
                || rechnungsgruppe[rechnungsgruppe_pk] = 9 && MTH in {7,8,9,10,11,12}
                    , DATE(YR , 12 , 1)
            , rechnungsgruppe[rechnungsgruppe_pk] = 44 && MTH in {1,2}, DATE(YR , 2 , 1)
            , rechnungsgruppe[rechnungsgruppe_pk] = 44 && MTH in {3,4,5}, DATE(YR , 5 , 1)
            , rechnungsgruppe[rechnungsgruppe_pk] = 44 && MTH in {6,7,8}, DATE(YR , 8 , 1)
            , rechnungsgruppe[rechnungsgruppe_pk] = 44 && MTH in {9,10,11}, DATE(YR , 11 , 1)
            , rechnungsgruppe[rechnungsgruppe_pk] = 44 && MTH = 12, DATE(YR + 1, 2 , 1)
            , rechnungsgruppe[rechnungsgruppe_pk] = 46 && MTH = 1, DATE(YR , 1 , 1)
            , rechnungsgruppe[rechnungsgruppe_pk] = 46 && MTH in {2,3,4}, DATE(YR , 4 , 1)
            , rechnungsgruppe[rechnungsgruppe_pk] = 46 && MTH in {5,6,7}, DATE(YR , 7 , 1)
            , rechnungsgruppe[rechnungsgruppe_pk] = 46 && MTH in {8,9,10}, DATE(YR , 10 , 1)
            , rechnungsgruppe[rechnungsgruppe_pk] = 46 && MTH in {11,12}, DATE(YR + 1, 1 , 1)
            , rechnungsgruppe[rechnungsgruppe_pk] = 6 && MTH in {6,7,8,9,10,11,12}, DATE(YR + 1 , 5 , 1)
            , rechnungsgruppe[rechnungsgruppe_pk] = 6 && MTH in {1,2,3,4,5}, DATE(YR , 5 , 1)
            , rechnungsgruppe[rechnungsgruppe_pk] = 49 , DATE(YR , MTH , 1)
            , DATE(1999, MTH , 1)
            )