Forum Discussion
Open Date and Ship Date
Hi,
I have common issue with dates in DAX but this time with a small twist. I have 2 tables, transaction and date.
Transaction Table has two date columns and value column:
1- Order Date: The date I'm putting the order
2- Shipping Date: The data I'm shipping the order
3- Value
Date Table is full year Calendar
I am connecting both transaction date with calendar using order date (Active relationship) and with shipping date (inactive)
Challenge:
I want to calculate the total shipment for the first 10 order dates of month that will be shipped in full month of the order date + 2
For example, I have orders that I put in January which will be shipped in March. I want to create a measure that will calculate the total shipment by filtering order date to be from 1-10 and ship date to be full March. My month slicer will be based on the Date Table. If I choose Jan, the measure should return 100.
Please help.
Thanks π
Omega
Try thisTotal = VAR _month = SELECTEDVALUE ( 'Date'[Date].[MonthNo] ) VAR _result = CALCULATE ( SUM ( 'Transaction'[Value] ), FILTER ( 'Transaction', MONTH ( 'Transaction'[Order Date] ) = _month && DAY ( 'Transaction'[Order Date] ) < 11 && MONTH ( 'Transaction'[Shipping Date] ) = MOD ( _month + 2, 12 ) ) ) RETURN _resultOutput
Did I answer your question? Mark my post as a solution!
Appreciate with a kudos π
1 Reply
- nandukrishnavs
Community Champion
Omega
Try thisTotal = VAR _month = SELECTEDVALUE ( 'Date'[Date].[MonthNo] ) VAR _result = CALCULATE ( SUM ( 'Transaction'[Value] ), FILTER ( 'Transaction', MONTH ( 'Transaction'[Order Date] ) = _month && DAY ( 'Transaction'[Order Date] ) < 11 && MONTH ( 'Transaction'[Shipping Date] ) = MOD ( _month + 2, 12 ) ) ) RETURN _resultOutput
Did I answer your question? Mark my post as a solution!
Appreciate with a kudos π