Custom table with inactive date relationships

[Usates]

I need a custom table using both the ship date and order date by different time frames. How do I build this to look something like this? Sample file included

Test.pbix (627.5 KB)

[Harsh]

Hello @Usates,

Thank You for posting your query onto the Forum.

Well, firstly had a bi-directional relationships between the “Data Table” and “Date Table” which is absolutely not recommended at all. And therefore have changed the relationship status from “1:1” to “1:M”. Below is the screenshot of the relationship status provided for the reference -

Now, you had created a table with customized columns i.e. with spaces contained in it. Below is the screenshot of your table provided for the reference -

So now, if you write your measures correct than also it will show blanks due to the spaces contained into your column. Alongside customized column you’ll be required to create a normal column without any spaces contained in it in order to achieve the results. Below is the screenshot provided for the reference -

Now, it’s just a matter of writing normal formulas. Below are the formulas provided for the reference -

1. Formulas For Order Date -

Total Sales By Order Date = SUM( Data[Amount] )

Total Sales By Order Date MTD = 
CALCULATE( [Total Sales By Order Date] , 
    DATESMTD( DateDimension[Date] ) )


Total Sales By Order Date QTD = 
CALCULATE( [Total Sales By Order Date] , 
    DATESQTD( DateDimension[Date] ) )

2. Formulas for Ship Date -

Total Sales By Ship Date = 
CALCULATE( [Total Sales By Order Date] , 
    USERELATIONSHIP( Data[Ship Date] , DateDimension[Date] ) , 
    Data[Status] = "Open" )


Total Sales By Ship Date MTD = 
CALCULATE( [Total Sales By Ship Date] , 
    DATESMTD( DateDimension[Date] ) )


Total Sales By Ship Date QTD = 
CALCULATE( [Total Sales By Ship Date] , 
    DATESQTD( DateDimension[Date] ) )

3. Consolidated Formula for Status by MTD -

Status MTD = 
VAR CurrentItem = SELECTEDVALUE( 'Table'[Normalized] )

RETURN
SWITCH( TRUE() , 
    CurrentItem = "Orders Entered" , [Total Sales By Order Date MTD] ,
    CurrentItem = "Open Orders by Ship Date" , [Total Sales By Ship Date MTD] ,
    CurrentItem = "Total" , [Total Sales By Order Date MTD] + [Total Sales By Ship Date MTD] ,
BLANK() )

4. Consolidated Formula for Status by QTD -

Status QTD = 
VAR CurrentItem = SELECTEDVALUE( 'Table'[Normalized] )

RETURN
SWITCH( TRUE() , 
    CurrentItem = "Orders Entered" , [Total Sales By Order Date QTD] ,
    CurrentItem = "Open Orders by Ship Date" , [Total Sales By Ship Date QTD] ,
    CurrentItem = "Total" , [Total Sales By Order Date QTD] + [Total Sales By Ship Date QTD] ,
BLANK() )

Lastly, below is the screenshot of the result provided for the reference -

I’m also attaching the working of the PBIX file for the reference.

Hoping you find this useful and meets your requirements that you’ve been looking for.

Thanks and Warm Regards,
Harsh

Test - Harsh.pbix (630.1 KB)