Check whether the Date exists between Current Date and Last 2 month

[ncbshiva]

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]

@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 ) )

[ncbshiva · Accepted solution]

@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 

[parry2k]

@ncbshiva use date from date dimension