Forum Discussion
DatesInPeriod with duplicate dates in fact table Unique in Calender table
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 :
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:
1 Reply
- DataNinja777
Super User
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: