Date changing when uploading to service

[jackperry61]

Good afternoon,

I have a report which I am uploading to the PowerBI service from my desktop app. In the desktop app there is a list of contracts called PBI_RentalContractLines. Each Contract has a rental billing start date, and a rental billing end date. I am using the below function to create a new table, with one line per each day between the rental billing start/end dates. Each line is then tagged as either "Ancillary Revenue" or "Rental Revenue" depending on an if function. This data is then put into a matrix with each contract's revenue split out over the contract period, and the ancillary revenue allocated to the start date.

This works perfectly in the desktop app, but the issue I am having is when I upload the same report to the Service, the data initially shows fine, but after an incremental refresh the 'LineRentalDate' on the ancillary revenue changes to 30/12/1899.

I have checked all of the formatting and all of the date columns have the same formatting, which is Data Type: Date/Time and Format: (General Date)

Here is the data in the desktop app, and how it appears at first in the service:

Here is how the data appears after an incremental refresh in the service:

This is the formula used to generate the table:

DailyBilling_RentalLineRentalAmountsWithDates =

FILTER(

    GENERATE(

    PBI_RentalContractLine,

    VAR StartDate = PBI_RentalContractLine[FC_RentalBillingStartDate]

    VAR EndDate = PBI_RentalContractLine[FC_RentalBillingEndDate]

    RETURN

        SELECTCOLUMNS(

            CALENDAR(StartDate, EndDate),

            "LineRentalDate", DATE(YEAR([Date]), MONTH([Date]), DAY([Date]))

        )

)

Thanks for your help in advance!

Jack

[KNP]

Can you share a screenshot of your model/relationships?

It seems more likely that the dates being defined are not broad enough to cover the data and likely a result of the service wanting to do everything in UTC (which is incredibly frustrating). Especially with IR.

My thinking is, if it is a UTC issue and it is affecting the dates enough to kick it back to the last day of the previous year, and your model doesn't cater for it, it could be getting a default 1899 date.

[jackperry61]

Good morning, 

Screenshot of the relationships below. I should note the FC_RentalBillingStartDate and FC_RentalBillingEndDate that are converted into the LineRentalDate in the DAX formula are calculated using the below two DAX formulas (basically just to fill in any blanks, if there are any in the original data).

The bit that I'm really struggling to understand is why it is only doing this for the "Ancillary Revenue" data rather than the "Rental Revenue", even though it is all pulling from the same table.

FC_RentalBillingStartDate =

IF (

    ISBLANK(PBI_RentalContractLine[RentalBillingStartDate]),

    IF (

        ISBLANK(LOOKUPVALUE(PBI_RentalContractHeader[ShipmentDate], PBI_RentalContractHeader[No], PBI_RentalContractLine[DocumentNo])),

        PBI_RentalContractLine[RequestedDeliverydate],

        LOOKUPVALUE(PBI_RentalContractHeader[ShipmentDate], PBI_RentalContractHeader[No], PBI_RentalContractLine[DocumentNo])

    ),

    PBI_RentalContractLine[RentalBillingStartDate]

)

FC_RentalBillingEndDate = IF(
PBI_RentalContractLine[FC_RentalBillingStartDate]>PBI_RentalContractLine[RentalBillingEndDate],
PBI_RentalContractLine[FC_RentalBillingStartDate],
IF(ISBLANK(PBI_RentalContractLine[RentalBillingEndDate]),PBI_RentalContractLine[RequestedDeliveryDate],PBI_RentalContractLine[RentalBillingEndDate]))

[BITomS]

Hi @jackperry61, you mention you have used an incremental refresh in the service? So the 1899 date could potentially be some old data that is persistent as it won't be affected by subsequent refreshes. Have you tried republishing the full report to the service so your desktop version 'overwrites' whatever is currently in the service? Do you need an incremental refresh?

Otherwise, it might be good to share how your incremental refresh has been set up. It feels like this may be the problem, rather than any DAX.

[jackperry61]

Good morning,

Sorry it was a long day yesterday - I just mean a scheduled refresh. Every time it is uploaded to the service it looks fine, but then if I refresh the data, or it refreshes at midnight when the schedule is set, the dates revert to 31/12/1899.

Thanks