Forum Discussion
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...
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.
Tried to streamline it a bit more, but same problem.
The weird part is that right after the calculation finishes, it does show me the correct periods as a result, but as soon as I save the file it reverts to the previous weird DATE error state.
REG_nextbill = VAR TD = TODAY() - 5
VAR YR = YEAR(TD)
VAR MTH = MONTH(TD)
RETURN
switch(TRUE()
, rechnungsgruppe[rechnungsgruppe_pk] = 49 , DATE(YR , MTH , 1)
, rechnungsgruppe[rechnungsgruppe_pk] = 46 && MTH = 1, DATE(YR , 1 , 1)
, rechnungsgruppe[rechnungsgruppe_pk] = 44 && MTH in {1,2}, DATE(YR , 2 , 1)
, rechnungsgruppe[rechnungsgruppe_pk] IN {43 , 45 , 48} && MTH in {1,2,3}, DATE(YR , 3 , 1)
, rechnungsgruppe[rechnungsgruppe_pk] = 46 && MTH in {2,3,4}, DATE(YR , 4 , 1)
, rechnungsgruppe[rechnungsgruppe_pk] = 44 && MTH in {3,4,5}
|| rechnungsgruppe[rechnungsgruppe_pk] = 6 && MTH in {1,2,3,4,5}
, DATE(YR , 5 , 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] = 46 && MTH in {5,6,7}, DATE(YR , 7 , 1)
, rechnungsgruppe[rechnungsgruppe_pk] = 44 && MTH in {6,7,8}, DATE(YR , 8 , 1)
, rechnungsgruppe[rechnungsgruppe_pk] IN {43 , 45 , 48} && MTH in {7,8,9}, DATE(YR , 9 , 1)
, rechnungsgruppe[rechnungsgruppe_pk] = 46 && MTH in {8,9,10}, DATE(YR , 10 , 1)
, rechnungsgruppe[rechnungsgruppe_pk] = 44 && MTH in {9,10,11}, DATE(YR , 11 , 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] = 46 && MTH in {11,12}, DATE(YR + 1, 1 , 1)
, rechnungsgruppe[rechnungsgruppe_pk] = 44 && MTH = 12, DATE(YR + 1, 2 , 1)
, rechnungsgruppe[rechnungsgruppe_pk] = 6 && MTH in {6,7,8,9,10,11,12}, DATE(YR + 1 , 5 , 1)
, DATE(1999, MTH , 1)
)
Hi zahlenschubser ,
Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.
If the information is sensitive please share it trough private message.