I have an issue with returning the right date on visuals. My model has lots of dates columns in the fact table and the methodology is such that only Date IDs and Time IDs should be exposed in the fact table. And there should be a folder where dates and times should be exposed to uses for slicing and dicing the data.
I have tried to implement that into the model but when I tested out these days, I find that they are not always correct as seen in this image:
An example of some of the DAX codes are:
Delivery Date =
VAR _DeliveryDate = CALCULATE ( FIRSTDATE ( 'Date'[Date] ), TREATAS ( VALUES ( Orders[Delivery Date ID] ), 'Date'[Date ID] ) ) RETURN _DeliveryDate
Delivery Time =
VAR _DeliveryTime = CALCULATE ( MIN ( 'Time'[Time]), TREATAS ( VALUES ( Orders[Delivery Time ID] ), 'Time'[Time] ) ) RETURN _DeliveryTime
Delivery Date & Time = [Delivery Date] + [Delivery Time]
From the image above, I don’t know why Delivery Date column is not returning any data. I have at this moment added some dates columns into the Fact table for a test but the idea is not to have them in the fact table but create and organised them in a folder as I have done except that it is not working properly.
I have added a model
SuperStoreEnterpriseModel.pbix (2.6 MB)
I do anticipate your help.