what is the DAX measure for total cost from two dimension tables?

[Sreeragkv07]

dax measure for getting total cost from fact table by comparing the dates columns from two diffrent dimension tables.

   i have a fact table with two  date ID fields and a cost column. 

startdatekey       enddatekey         cost
---------------     --------------      -------
1                                 a                   100
2                                 b                   100
3                                 c                    100
4                                 d                    100

And have Two different date table

startdatekey       Startdate        
---------------     --------------      
1                               1\1\1956 
2                                1\1\1980             
3                                1\1\1978               
4                                 1\1\1987     

enddatekey       enddate        
---------------     --------------      
1                               1\1\1954 
2                                1\1\1981            
3                                1\1\1970              
4                                 1\1\1983           

Total Cost=Sum of Cost when Start date<end date .There is no relationship between these tables.the Output should look like this 

startdate      end date     Total Cost

1\1\1980       1\1\1981     100

how can i create a measure to get Total cost with this condition.

[lbendlin]

Please correct the sample data as per your description.  US locale uses forward slash / ,  not backslash \

If your users provide end dates before start dates then you need to aask them for better data.

[Sreeragkv07]

Actually its    forward slash.It was a Typing mistake

[Anonymous]

Hi @Sreeragkv07,

I think these operations should similar to common calculations, you need to lookup correspond date values based on keys and use them with datediff function to get the diff and calculate with cost field get total spends.
BTW, it seems like the source data is not correct, I suppose you need to do some data cleanup and transform to convert them to normal date values before calculate.
Regards,

Xiaoxin Sheng

[Sreeragkv07]

Hi Ibendlin,

  -  Actually the userdata is given  same as mentioned.

   - end date key is in whole number  and the start date and end date in Date format.

  -   US Locale

[lbendlin]

Please correct your sample data

- some end dates are before the start dates

- end date key is numeric or alpha?

- which locale is this date format from?