DatesInPeriod with duplicate dates in fact table Unique in Calender table

[subhashg]

Unable to get 3M rolling sum using datesInPeriod inspite of calender marked as date table.

I have 2 tables.

Fact table(Table)- Duplicate date keys,

Calender table - Unique keys & marked as date table

Relationship - 1(Calender) to Many(Fact table)

Below is the data . 

Fact table : 

Table
Item number	Qty	Fact date
121	45	1-Mar-23
131	45	1-Apr-23
141	30	1-May-23
151	40	1-May-23
161	50	1-Jul-23
121	45	1-Aug-23

Calender table:

Calender

Date

1-Mar-23

2-Mar-23

3-Mar-23

4-Mar-23

5-Mar-23

6-Mar-23

7-Mar-23

8-Mar-23

9-Mar-23

10-Mar-23

11-Mar-23

Both below dax not giving the right 2M rolling sum. It is not summing up based on dates filter.

3M DatesInPeriod =
CALCULATE(sum('Table'[Qty]),DATESINPERIOD('Calender'[Date],max('Table'[Fact date]),-2,MONTH))

3m DatesBetween =
VAR _FDate= EDATE(max('Table'[Fact date]),-1)
VAR _LDate=max('Table'[Fact date])
Return
CALCULATE(sum('Table'[Qty]),DATESBETWEEN('Calender'[Date],_FDate,_LDate))

Result :

[DataNinja777 · Accepted solution]

Hi @subhashg ,

The reason it is showing incorrect figures is because the formula is referencing the fact table date column instead of the calendar table.   It is recommended that we always use fields from a dimension table in our visualization instead of the fact table in order to avoid unintentional issues like this.  Modifying your dax formula to reference the calendar table date instead of the fact table date produces the following output for datesbetween.  

Also, for datesinperiod, the same output was produced.

I attach the pbix file for your reference:

DatesInPeriod task.pbix