Forum Discussion
Help on getting data from Same Calendar date but 2 different conditions on 2 different tables
Hi Greg,
First of all thank you for replying back..
I tried the expression, It doesnt work.
There is no NULL as text in the date, when I pull data from SQL table, it replaces blanks with NULLS.
The calculation issue that I have is in the first table SQL table I have a total list of Occ-Numbers. I need to filter the occ_num based on date I select from Date Calendar.
For. e.g. if i have 100 occ_number before 15th April it should provide me list of all the occ_number when I select the date as 15th April from Date Calendar. I will link the occ_num from Date Table to term date from SQL table. Alternatively, If i Select the date as 8th April it will give me the count as 80, because there were 20 occ_num registered from 8th-15th April
So what I really need to do is like calculate a measure - something like this
Total OCC_NUM= CALCULATE(COUNT(SQL_Test[occ_num]),SQL_Test[comm_dat]<=(Fiscal_calendar[Date]) || SQL_Test[term_dat] ISNULL))
where the Fiscal_calendar[Date is which I will select in Date Calendar
Any ideas on how I can achieve this?
Regards,
Prathamesh Sable
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