Forum Discussion
Help on getting data from Same Calendar date but 2 different conditions on 2 different tables
Hi, I need help on some dyamic date filteration in a report that I am trying to create - I have loaded data from a SQL sample table - occ_num area_dsc3 area_dsc4 num comm_dat term...
I didn't get that you just wanted the total count at all from your original post. I thought you were trying to filter a table.
Total Count =
VAR MyDate = MAX('Calendar'[Date])
VAR tmpTable = ALL(Flats)
VAR tmpTable1 = ADDCOLUMNS(tmpTable,"Count", IF(([comm_dat]<=MyDate && ISBLANK([term_dat])) || ([comm_dat]<=MyDate && [term_dat]>MyDate),1,0))
VAR tmpTable2 = FILTER(tmpTable1,[Count]=1)
RETURN COUNTROWS(tmpTable2)Hi Greg,
Based on the same other table2 which is below
where ( "end-dte" Is Null
OR
"end-dte" >= '2018-04-15'
AND B.[user-cde] IS NOT NULL
)
I need to get the following
Total OCC_NUM2= CALCULATE(COUNT(Table2[occ_num]), table2.end-dte >= (Fiscal_calendar[Date]) || table2.end-dte ISNULL))
| r-cde | trn-val | end-dte | occ_num | area_dsc3 | area_dsc4 | num | comm_dat | term_dat | pr_seq_no | full_addr |
| 278098X | 19.76 | 15/04/2018 | 20045216 | a | South Area Service Centre | 4493 | 16/02/1987 | null | 20423 | FLAT 103 Semley House, Semley Place, London, SW1W 9QL |
| 3110315 | 109.28 | 15/04/2018 | 20146368 | d | North Area Service Centre | 167011 | 28/08/2017 | null | 23627 | FLAT 56 Burlington Close, Burlington Close, Walterton Road Estate, London, W9 3LZ |
| 2057509 | 12.33 | 15/04/2018 | 20054393 | x | Central Area Service Centre | 8543 | 18/02/2002 | null | 13652 | FLAT 35 Lambourne House, Broadley Street, Church Street Estate, London, NW8 8DN |
| 2797448 | 143.59 | 15/04/2018 | 20047918 | c | South Area Service Centre | 5623 | 19/07/2010 | null | 16468 | FLAT 20 Stourhead House, Tachbrook Street, Lillington Gardens Estate, London, SW1V 2QE |
| 2277128 | 13.53 | 15/04/2018 | 20063051 | c | West Area Service Centre | 8002 | 11/07/2005 | null | 15747 | FLAT 17 Bridgewater House, Hallfield Estate, Hallfield Estate, London, W2 6EE |
| 2004678 | 1.05 | 15/04/2018 | 20045719 | c | South Area Service Centre | 4779 | 31/05/1999 | null | 18076 | FLAT 65 Fountain Court, Buckingham Palace Road, London, SW1W 9SU |
Later on I will have to join both these dates based on the date calendar & get the data based on the date selected in the calendar.
Thank you very much for your help
Regards,
Prathamesh Sable