Forum Discussion
Advocate VCheck whether the Date exists between Current Date and Last 2 month
Hi Team,
I want to count the deliveries made between selected month&year and last 2 month&year.
For example i have ordered a phone and it got delivered on 15th May 2019. I have a Date Dimension present in my model and if i select month as "May" and Year as "2019". Then it should check from current + 2 months prior.
In the above example my phone got delivered on 15th May 2019 so the delivery date falls between current month and 2 months prior. I want to count these records for the requirement.
I have a date dimension and delivery table both are linked by date and delivery date.
Please help to achieve this count by writing a Dax formula.
Regards
SHIVA
parry2k Thanks for the reply.
As mentioned i have a Date Dimension table and Transaction table where i have product and Delivery Date.
Relationship has been setup between two table with Date and Delivery Date.
Not sure which date to use in the DAX formula.
I am using Month and Year filter from the Date Dimension and i need to check whether my delivery date falls between selected Month,Year and the Delivery Date. Please find below the sample data and expected result as delivery count.
Delivery Date Product Code Product Delivery Count 15-5-2019 11055 Mobile 1 10-4-2019 11055 Laptop 1 21-2-2019 11065 Ear phone 0 5-3-2019 11077 Adapter 1 Please help me with the DAX expression for this.
Regards
SHIVA
3 Replies
- parry2k
Super User
ncbshiva try this measure, so if we are checking as of May 2019, it is going to give the deliveries between April , 2019 and May 2019, if you need to change the period, replace -2 in following expression with number of months you want to go back.
Deliveries = CALCULATE( COUNTROWS(FACT_TRANSACTIONS ), DATESINPERIOD('Date'[Date], MAX( 'Date'[Date] ), -2, MONTH ) )
ncbshivaparry2k Thanks for the reply.
As mentioned i have a Date Dimension table and Transaction table where i have product and Delivery Date.
Relationship has been setup between two table with Date and Delivery Date.
Not sure which date to use in the DAX formula.
I am using Month and Year filter from the Date Dimension and i need to check whether my delivery date falls between selected Month,Year and the Delivery Date. Please find below the sample data and expected result as delivery count.
Delivery Date Product Code Product Delivery Count 15-5-2019 11055 Mobile 1 10-4-2019 11055 Laptop 1 21-2-2019 11065 Ear phone 0 5-3-2019 11077 Adapter 1 Please help me with the DAX expression for this.
Regards
SHIVA
