Forum Discussion
Calulate open items from Previous month B/Forward
Hi Experts
My relationship between my Dim_Date Table and FACT table is based on Date and Created Date (FACT Table)
I am trying to work out how many items remained open in the previous periods that have been carried forward to the current period.
The following DAX gives me the Difference of applying the Relationship Between the created date and closed date..
ie. Date to Closing Date = 4,186
Date to Created Date -= 2,983
Difference is 1,203....but i need to show the 2,983 in my end result.....TOTALLY STUCK
Created Date All Dates Before 01.09.2020
Closed Date is on or after 01.09.2020
I have applied a month filter from the date table - when i select Sept i would expect to see the following results.
| Created_Date | Closed | Date_Closed | Fruit |
| 14 August 2020 | 1 | 03-Sep-20 | Apples |
| 10 August 2020 | 1 | 07-Sep-20 | Apples |
| 23 July 2020 | 1 | 17-Sep-20 | Apples |
| 19 August 2020 | 1 | 08-Sep-20 | Apples |
| 11 August 2020 | 1 | 02-Sep-20 | Apples |
| 28 July 2020 | 1 | 03-Sep-20 | Apples |
| 03 April 2020 | 1 | 02-Sep-20 | Apples |
| 22 November 2019 | 1 | 08-Sep-20 | Apples |
| 20 August 2020 | 1 | 01-Sep-20 | Apples |
| 20 August 2020 | 1 | 04-Sep-20 | Apples |
| 17 August 2020 | 1 | 07-Sep-20 | Apples |
| 17 August 2020 | 1 | 01-Sep-20 | Apples |
| 17 August 2020 | 1 | 09-Sep-20 | Apples |
| 11 October 2019 | 1 | 15-Sep-20 | Apples |
| 05 August 2020 | 1 | 02-Sep-20 | Apples |
| 25 August 2020 | 1 | 01-Sep-20 | Banana |
| 17 August 2020 | 1 | 03-Sep-20 | Banana |
| 28 August 2020 | 1 | 07-Sep-20 | Banana |
| 25 August 2020 | 1 | 04-Sep-20 | Banana |
| 17 August 2020 | 1 | 09-Sep-20 | Banana |
| 28 August 2020 | 1 | 04-Sep-20 | Banana |
| 21 August 2020 | 1 | 04-Sep-20 | Banana |
| 17 August 2020 | 1 | 04-Sep-20 | Banana |
| 31 August 2020 | 1 | 01-Sep-20 | Banana |
| 31 August 2020 | 1 | 01-Sep-20 | Banana |
| 31 August 2020 | 1 | 01-Sep-20 | Banana |
| 29 August 2020 | 1 | 01-Sep-20 | Banana |
| 31 August 2020 | 1 | 01-Sep-20 | Banana |
| 31 August 2020 | 1 | 01-Sep-20 | Banana |
| 31 August 2020 | 1 | 01-Sep-20 | Banana |
| 31 August 2020 | 1 | 01-Sep-20 | Banana |
| 26 August 2020 | 1 | 01-Sep-20 | Banana |
| 27 August 2020 | 1 | 09-Sep-20 | Banana |
| 27 August 2020 | 1 | 09-Sep-20 | Banana |
| 27 August 2020 | 1 | 09-Sep-20 | Banana |
| 26 August 2020 | 1 | 09-Sep-20 | Banana |
| 25 August 2020 | 1 | 01-Sep-20 | Banana |
| 21 August 2020 | 1 | 01-Sep-20 | Oranges |
| 21 August 2020 | 1 | 01-Sep-20 | Oranges |
| 20 August 2020 | 1 | 01-Sep-20 | Oranges |
| 26 August 2020 | 1 | 09-Sep-20 | Oranges |
| 25 August 2020 | 1 | 01-Sep-20 | Oranges |
| 19 August 2020 | 1 | 01-Sep-20 | Oranges |
| 24 August 2020 | 1 | 01-Sep-20 | Oranges |
| 24 August 2020 | 1 | 01-Sep-20 | Oranges |
| 24 August 2020 | 1 | 03-Sep-20 | Oranges |
| 12 August 2020 | 1 | 17-Sep-20 | Oranges |
| 27 August 2020 | 1 | 14-Sep-20 | Oranges |
| 28 August 2020 | 1 | 10-Sep-20 | Oranges |
| 28 August 2020 | 1 | 17-Sep-20 | Oranges |
| 25 August 2020 | 1 | 01-Sep-20 | Oranges |
| 28 August 2020 | 1 | 02-Sep-20 | Oranges |
| 28 August 2020 | 1 | 02-Sep-20 | Oranges |
| 24 August 2020 | 1 | 08-Sep-20 | Oranges |
| 24 August 2020 | 1 | 03-Sep-20 | Oranges |
| 19 August 2020 | 1 | 07-Sep-20 | Oranges |
| 18 August 2020 | 1 | 04-Sep-20 | Oranges |
| 28 August 2020 | 1 | 01-Sep-20 | Oranges |
| 20 August 2020 | 1 | 04-Sep-20 | Oranges |
| 20 August 2020 | 1 | 01-Sep-20 | Oranges |
| 20 August 2020 | 1 | 14-Sep-20 | Oranges |
| 25 June 2020 | 1 | 02-Sep-20 | Oranges |
| 28 August 2020 | 1 | 04-Sep-20 | Oranges |
| 13 August 2020 | 1 | 02-Sep-20 | Oranges |
| 25 August 2020 | 1 | 09-Sep-20 | Oranges |
| 13 August 2020 | 1 | 09-Sep-20 | Oranges |
| 27 August 2020 | 1 | 02-Sep-20 | Oranges |
| 27 August 2020 | 1 | 04-Sep-20 | Oranges |
| 21 August 2020 | 1 | 10-Sep-20 | Oranges |
| 19 August 2020 | 1 | 11-Sep-20 | Oranges |
| 12 August 2020 | 1 | 09-Sep-20 | Oranges |
| 03 August 2020 | 1 | 09-Sep-20 | Oranges |
| 19 August 2020 | 1 | 02-Sep-20 | Oranges |
| 18 August 2020 | 1 | 01-Sep-20 | Oranges |
| 06 July 2020 | 1 | 08-Sep-20 | Oranges |
| 03 July 2020 | 1 | 04-Sep-20 | Oranges |
| 24 August 2020 | 1 | 04-Sep-20 | Oranges |
| 18 August 2020 | 1 | 09-Sep-20 | Oranges |
| 27 August 2020 | 1 | 04-Sep-20 | Oranges |
| 26 August 2020 | 1 | 02-Sep-20 | Oranges |
| 24 August 2020 | 1 | 07-Sep-20 | Oranges |
| 17 August 2020 | 1 | 09-Sep-20 | Oranges |
| 13 August 2020 | 1 | 04-Sep-20 | Oranges |
| 26 August 2020 | 1 | 02-Sep-20 | Oranges |
| 21 July 2020 | 1 | 01-Sep-20 | Oranges |
| 16 July 2020 | 1 | 04-Sep-20 | Oranges |
| 24 August 2020 | 1 | 02-Sep-20 | Oranges |
| 04 September 2019 | 1 | 15-Sep-20 | Oranges |
| 25 August 2020 | 1 | 01-Sep-20 | Oranges |
| 02 December 2019 | 1 | 04-Sep-20 | Oranges |
| 25 August 2020 | 1 | 15-Sep-20 | Oranges |
| 28 August 2020 | 1 | 17-Sep-20 | Oranges |
| 28 August 2020 | 1 | 17-Sep-20 | Oranges |
| 28 August 2020 | 1 | 17-Sep-20 | Oranges |
| 21 August 2020 | 1 | 12-Sep-20 | Oranges |
| 14 May 2020 | 1 | 10-Sep-20 | Oranges |
| 24 August 2020 | 1 | 01-Sep-20 | Oranges |
| 24 August 2020 | 1 | 01-Sep-20 | Oranges |
| 27 August 2020 | 1 | 09-Sep-20 | Oranges |
Hi Anonymous ,
It seems like you want to calculate the count within the selected date period. You could use the following formula with Slicer :
count = VAR _min = MIN ( 'DateSlicer'[Date] ) VAR _max = MAX ( 'DateSlicer'[Date] ) RETURN CALCULATE ( COUNT ( 'STG Fact_To_Do'[Closed] ), FILTER ( 'STG Fact_To_Do', 'STG Fact_To_Do'[Created_Date] <= _max && 'STG Fact_To_Do'[Date_Closed] >= _min ) )Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please upload some insensitive data samples and expected output.
Best Regards,
Eyelyn Qin
4 Replies
- amitchandak
Super User
Anonymous , Prefer date is coming from an independent date slicer
Measure =
var _min = minx(allselected(Date), Date[Date])
return
calculate(countrows([Table]), filter(Table, Table[Created_Date]<_min && Table[Created_Date]>=_min))or
Measure =
var _min = minx(allselected(Date), Date[Date])
return
calculate(countrows([Table]), filter(all(Table), Table[Created_Date]<_min && Table[Created_Date]>=_min)) - AllisonKennedy
Community Champion
Anonymous
Also try changing relationship to use Closed Date instead of Created Date (or create a new measure using the USERELATIONSHIP if you want the created date filter in other areas of your report).
Based on your explanation, it seems like you want to filter the DimDate[Date] and get a list of all items that have been closed in that time period, so the relationship needs to be for Closed Date to make this work, otherwise you won't see any items that were created in other periods. Does that make sense?Hi Allison
How would you write the dax using user relationships in the measure based on date closed and date filed from Dim Date.
count = VAR _min = MIN ( 'DateSlicer'[Date] ) VAR _max = MAX ( 'DateSlicer'[Date] ) RETURN CALCULATE ( COUNT ( 'STG Fact_To_Do'[Closed] ), FILTER ( 'STG Fact_To_Do', 'STG Fact_To_Do'[Created_Date] <= _max && 'STG Fact_To_Do'[Date_Closed] >= _min ) )
Hi Anonymous ,
It seems like you want to calculate the count within the selected date period. You could use the following formula with Slicer :
count = VAR _min = MIN ( 'DateSlicer'[Date] ) VAR _max = MAX ( 'DateSlicer'[Date] ) RETURN CALCULATE ( COUNT ( 'STG Fact_To_Do'[Closed] ), FILTER ( 'STG Fact_To_Do', 'STG Fact_To_Do'[Created_Date] <= _max && 'STG Fact_To_Do'[Date_Closed] >= _min ) )Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please upload some insensitive data samples and expected output.
Best Regards,
Eyelyn Qin
