Forum Discussion
Help on getting data from Same Calendar date but 2 different conditions on 2 different tables
This solution assumes a DISCONNECTED Calendar table (no relationships) that is used as the slicer.
Based on the data provided and that you really have the text "null" in the date there are some hoops to jump through, but a measure like this should work:
Measure =
VAR MyDate = MAX('Calendar'[Date])
VAR MyCommDate = MAX('Flats'[comm_dat])
VAR MyTermDate = MAX('Flats'[term_dat])
VAR TermIsNull = IF(MyTermDate="null", TRUE(),FALSE())
VAR MyTermDate1 = IF(TermIsNull,DATE(1900,1,1),DATEVALUE(MyTermDate))
VAR FirstCase = IF( MyCommDate<=MyDate && TermIsNull,1,BLANK())
RETURN IF(FirstCase,1, IF(MyCommDate<=MyDate && MyTermDate1>MyDate,1,BLANK()))
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