Forum Discussion

zahlenschubser's avatar
zahlenschubser
Helper 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.

     

zahlenschubser's avatar
zahlenschubser
Helper 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)
            )
MFelix's avatar
MFelix
Super User
2 years ago

Hi zahlenschubser ,

 

What is the result you want to achieve? Can you give an example?

Again believe that the problem is the order of the options that will return the incorrect order for the result you want to achieve.

  • zahlenschubser's avatar
    zahlenschubser
    Helper IV
    2 years ago

    I have a bunch of customer groups that have different billing cycles. Depending on the cycle I want to get the current next billing period for each group.

    1) Every three months, but they start in Jan / Feb / Mar and then +3 months

    2) Monthly

    3) Half Yearly

    4) Yearly

    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.