[SSRS/Paginated Report] DATEDIFF #error and more date error

[Benedikt_Sch]

Hello,

I am tring to migrate an paginated SSRS into Power BI Service via PBI report builder.

Datasource is a shared Power BI Datasource.

I noticed a number of strange behaviors when I uploaded the message to power Bi service. In Report Builder almost everything looks fine..

All Dates are formatted the same way (dd.MM.yyyy german format)

Error 1: Datediff

I am not able to get a datediff in Power BI service.

Expression:

=DateDiff(
"d",
Fields!Date1.Value,
Fields!Date2.Value)

Report Builder:

Power Bi service:

Error 3 display Dates

I get #error when Format dates this way:

Format = "d"

Expression:

=cdate(Fields!Angefordertes_DCIN__Bestellposition_.Value)

Error 3 fixed colums:

when I scroll to the right a few columns should remain fixed.
In the report builder this works without problems.
When I try this in the Power Bi service, the fixed columns get larger when scrolling

origin Layout:

when scrolled

Error 4 dates min max:

This error occours on report Bulider and PBI Service.

I got a line with 2 dates:

24.06.2022 (dd.MM.YYYY)  and 03.08.2022

cDate(max(Fields!Aktuelles_DCIN__Lagerbuchung_.Value))

leads to the result: 24.06.2022..

I hope someone has already had experience with these problems and can give me some help here.

Thank and best wishes

Benedikt

[Anonymous]

Hi @Benedikt_Sch 

It's a bit hard for me to restore your scene, can you provide your sample for reference?

Best Regards,
Community Support Team _ Ailsa Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Benedikt_Sch]

Hi @Anonymous

Sorry for late reply! When I created an example I noticed a few things and I tried some more. 

To the first:

I have now been able to fix many of my problems. The reason is that Power BI seems to have problems with German data.

With the report setting "de-De" many problems occur in the Power BI service. In the report builder, however, most things work. In the SSRS portal, on the other hand, everything works without problems.

Short summary of my findings:

• Format: de-de has the following problems
   min/ max of date is still searched for english format. Means that it then searches for months instead of days.
•  Display of data in format "d" with the setting de-de does not work. Power Bi tries to display MM-dd-yyy instead of dd.MM.yyyy. For this reason all days after 12. are displayed with Error.

However there is thill a bug (?) for my Error 3 (scrolling)

If I create a table with data and want to fix the heading there then this only works in Power BI if I have no parameters.
As soon as I have a parameter, the fixed heading is no longer displayed.

Through tests I have found that the heading is fixed anyway. However, this is displayed in the HTML below the parameters and the menu bar, so that one can no longer see the fixation.

You can check this by dragging the heading very large.

Is this behavior a bug or have I set a setting incorrectly?

Dataset as expample

<Query>
<XmlData>
<Data>
<Row>
<Test>1</Test>
<A>Test</A>
<B>Test</B>
<C>Test</C>
<D>Test</D>
</Row>
<Row>
<Test>2</Test>
<A>Test</A>
<B>Test</B>
<C>Test</C>
<D>Test</D>
</Row>
<Row>
<Test>3</Test>
<A>Test</A>
<B>Test</B>
<C>Test</C>
<D>Test</D>
</Row>
<Row>
<Test>4</Test>
<A>Test</A>
<B>Test</B>
<C>Test</C>
<D>Test</D>
</Row>
<Row>
<Test>5</Test>
<A>Test</A>
<B>Test</B>
<C>Test</C>
<D>Test</D>
</Row>
<Row>
<Test>6</Test>
<A>Test</A>
<B>Test</B>
<C>v</C>
<D>v</D>
</Row>
<Row>
<Test>7</Test>
<A>Test</A>
<B>Test</B>
<C>Test</C>
<D>Test</D>
</Row>
<Row>
<Test>8</Test>
<A>Test</A>
<B>Test</B>
<C>Test</C>
<D>Test</D>
</Row>
<Row>
<Test>9</Test>
<A>Test</A>
<B>Test</B>
<C></C>
<D></D>
</Row>
<Row>
<Test>10</Test>
<A></A>
<B>Test</B>
<C>Test</C>
<D></D>
</Row>
<Row>
<Test>11</Test>
<A></A>
<B>Test</B>
<C></C>
<D></D>
</Row>
<Row>
<Test>12</Test>
<A></A>
<B></B>
<C></C>
<D></D>
</Row>
<Row>
<Test>13</Test>
<A></A>
<B></B>
<C></C>
<D></D>
</Row>
<Row>
<Test>14</Test>
<A>Test</A>
<B></B>
<C></C>
<D></D>
</Row>
<Row>
<Test>15</Test>
<A></A>
<B>Test</B>
<C></C>
<D>Test</D>
</Row>
<Row>
<Test>16</Test>
<A></A>
<B>Test</B>
<C></C>
<D></D>
</Row>
<Row>
<Test>17</Test>
<A></A>
<B></B>
<C></C>
<D>Test</D>
</Row>
<Row>
<Test>18</Test>
<A></A>
<B>Test</B>
<C>Test</C>
<D></D>
</Row>
<Row>
<Test>19</Test>
<A></A>
<B>Test</B>
<C></C>
<D></D>
</Row>
<Row>
<Test>20</Test>
<A>Test</A>
<B></B>
<C></C>
<D>Test</D>
</Row>
<Row>
<Test>21</Test>
<A></A>
<B></B>
<C></C>
<D></D>
</Row>
<Row>
<Test>22</Test>
<A></A>
<B></B>
<C></C>
<D></D>
</Row>
<Row>
<Test>23</Test>
<A></A>
<B></B>
<C></C>
<D></D>
</Row>
<Row>
<Test>24</Test>
<A></A>
<B></B>
<C></C>
<D></D>
</Row>
<Row>
<Test>25</Test>
<A></A>
<B></B>
<C></C>
<D></D>
</Row>
<Row>
<Test>26</Test>
<A></A>
<B></B>
<C></C>
<D></D>
</Row>
<Row>
<Test>27</Test>
<A></A>
<B></B>
<C></C>
<D></D>
</Row>
<Row>
<Test>28</Test>
<A></A>
<B></B>
<C></C>
<D></D>
</Row>
<Row>
<Test>29</Test>
<A></A>
<B></B>
<C></C>
<D></D>
</Row>
<Row>
<Test>30</Test>
<A></A>
<B></B>
<C></C>
<D></D>
</Row>
<Row>
<Test>31</Test>
<A></A>
<B></B>
<C></C>
<D></D>
</Row>
<Row>
<Test>32</Test>
<A></A>
<B></B>
<C></C>
<D></D>
</Row>
<Row>
<Test>33</Test>
<A></A>
<B></B>
<C></C>
<D></D>
</Row>
<Row>
<Test>34</Test>
<A></A>
<B></B>
<C></C>
<D></D>
</Row>
<Row>
<Test>35</Test>
<A></A>
<B></B>
<C></C>
<D></D>
</Row>
<Row>
<Test>36</Test>
<A></A>
<B></B>
<C></C>
<D></D>
</Row>
</Data>
</XmlData>
<ElementPath>Data{}/Row{Test(String),A(String),B(String),C(String),D(String)}</ElementPath>
</Query>

best wishes

Benedikt