Incorrect Date In Date Measures

upwardD · January 26, 2021, 6:03pm

Hello!
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.
Thank you

BrianJ · January 26, 2021, 7:01pm

@upwardD,

I think you’ve got the right idea, but just have some of your dates flipped around in the measures. Give this a go and see if it returns the results you are looking for.

Delivery Date =

VAR _DeliveryDate =

CALCULATE ( FIRSTDATE (Orders[Delivery Date] ),

TREATAS ( VALUES ( 'Date'[Date ID] ), Orders[Order Date ID] )

)

RETURN _DeliveryDate

– Brian

upwardD · January 26, 2021, 7:20pm

Hi @BrianJ
Thanks for the reply. Actually, the dates in the Orders table are not going to be there as we already have the date IDs. I only brought them in for the purpose of demonstrating what I want t achieve.
I am to utilise the date and time tables to leverage the concepts
Thank you

BrianJ · January 26, 2021, 7:27pm

@upwardD,

Thanks for the clarification. Should be a relatively straightforward fix. We’ll get back to you soon with a revised measure.

– Brian

BrianJ · January 26, 2021, 8:05pm

@upwardD,

Let’s see if this does the trick:

Delivery Date =

VAR _GrabID = SELECTEDVALUE( Orders[Delivery Date ID] )

VAR _DeliveryDate = 
CALCULATE (
    MIN( 'Date'[Date] ),
    FILTER(
        ALLSELECTED( 'Date' ),
        'Date'[Date ID] = _GrabID
    )
)

RETURN _DeliveryDate

– Brian

upwardD · January 26, 2021, 9:01pm

@BrianJ, thank you for the reply.
I see that if the filter is removed from the Year and Month slicer, the visual comes out perfectly but when I filter by Year, Month or both, it becomes ugly as seen in this image below:

What am I doing wrong, please?
Thank you

BrianJ · January 27, 2021, 1:38am

@upwardD,

My apologies – very busy day, and I didn’t check the alternate cases on the prior solution as carefully as I should have. Give this a go;

Delivery Date = 

VAR _GrabID = SELECTEDVALUE( Orders[Delivery Date ID] )
VAR _SelYr = SELECTEDVALUE( 'Date'[Year] )
VAR _SelMo = SELECTEDVALUE( 'Date'[Month Name] )

VAR _DeliveryDate = 
CALCULATE (
    MIN( 'Date'[Date] ),
    FILTER(
        ALL( 'Date' ),
        'Date'[Date ID] = _GrabID &&
        'Date'[Year] = _SelYr &&
        'Date'[Month Name] = _SelMo
    )
)

RETURN _DeliveryDate

Brian

upwardD · January 27, 2021, 9:46am

Hi @BrianJ,
Thank you for your reply. I honestly do appreciate your effort. But the issues remain on the highlighted columns. I Delivery date works fine but I still have issues with Delivery Date & Time, Feedback Date & Time as seen in this image:

Thank you so very much

BrianJ · January 27, 2021, 12:29pm

@upwardD,

I should have been clearer about that - I didn’t attempt to fix the other columns, figuring that you could apply the exact same pattern used to fix Delivery Date to those columns as well. If you have any problems in doing so, just give a shout.

Brian

upwardD · January 27, 2021, 12:35pm

Thanks @BrianJ. I did but it did not work.

BrianJ · January 27, 2021, 12:38pm

@upwardD,

OK - please post your current PBIX with what you tried and I’ll look at why that’s not working. Thanks!

Brian

upwardD · January 27, 2021, 1:15pm

Thanks for your patience.
The issue now is not with the Delivery Date but the columns with Dates & Times. I noticed that when, for example Delivery Date is blank, Delivery Date & Time should be blank. If you look at the Delivery Date Time (from the fact table), when the dates does not match what is on the filter, the formatted Delivery Date & Time return funny 30/12/1899… I want that part of the query to be blank as well and the query I wrote for this is:
Delivery Date & Time =
VAR _CheckBlank = [Delivery Date] + [Delivery Time]
RETURN
IF (
ISBLANK( [Delivery Date] && [Delivery Time] ), BLANK(), _CheckBlank
)

I equally tried:

VAR _CheckBlank = [Delivery Date] + [Delivery Time]

RETURN
IF (
ISBLANK( [Delivery Date] || [Delivery Time] ), BLANK(), _CheckBlank
)
It returned the same thing with the code above.

I am trying, with this code to check where it’s blank so I do not display the funny 30/12/1899 date on visual.

Thank you

BrianJ · January 27, 2021, 7:49pm

@upwardD,

OK, I think I’ve got a really robust solution for you now. I’ve changed the slicer harvesting approach from SELECTEDVALUE to an IN/VALUES construct. This allows for no, single or multiselect on both slicers. Here are the measures:

Delivery Date = 

VAR _GrabID = SELECTEDVALUE( Orders[Delivery Date ID] )
VAR _SelYr = VALUES( 'Date'[Year] )
VAR _SelMo = VALUES( 'Date'[Month Name] )

VAR _DeliveryDate = 
CALCULATE (
    MIN( 'Date'[Date] ),
    FILTER(
        ALL( 'Date' ),
        'Date'[Date ID] = _GrabID &&
        'Date'[Year] IN _SelYr &&
        'Date'[Month Name] IN _SelMo
    )
)

RETURN _DeliveryDate  

Delivery Time = 

    VAR _GrabID = SELECTEDVALUE( Orders[Delivery Time ID] )

    VAR _DeliveryTime = 
    CALCULATE (
        MIN( 'Time'[Time] ),
        FILTER(
            ALL( 'Time' ),
            'Time'[Time] = _GrabID
        )
    )

    RETURN _DeliveryTime

Delivery Date & Time =

IF( 
    [Delivery Date] = BLANK(), 
    BLANK(),
    FORMAT([Delivery Date], "MM/DD/YYYY") &" "&[Delivery Time] 
)

Feedback measures follow the exact same pattern.

I hope this hits the mark for you. Full solution file attached.

Brian
eDNA Forum - SuperStoreEnterpriseModel Solution.pbix (2.7 MB)

upwardD · January 28, 2021, 9:49am

Great solution delivered!
Thank you so very much @BrianJ. You are the best