Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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_dat | pr_seq_no | full_addr |
20150122 | St Johns Wood | Central Area Service Centre | 64448 | 09/04/2018 | null | 24563 | FLAT 19 Ashby Court, Pollitt Drive, Wharncliffe Gardens Estate, London, NW8 8UA |
20149968 | Mozart & Avenue Gardens | North Area Service Centre | 176292 | 09/04/2018 | null | 21957 | 32 Parry Road 40/60, Parry Road, Mozart Estate, London, W10 4SZ |
20150032 | Grosvenor | South Area Service Centre | 2484 | 09/04/2018 | null | 4356 | FLAT 31 Princess Mary House, Vincent Street, Grosvenor Estate, London, SW1P 4HL |
20149920 | Soho and Covent Garden | South Area Service Centre | 113496 | 09/04/2018 | null | 18870 | FLAT 5 Greens Court, 4 Greens Court, London, W1F 0HE |
20150076 | Soho and Covent Garden | South Area Service Centre | 6357 | 09/04/2018 | null | 19544 | FLAT 106 Vale Royal House, 36 Newport Court, London, WC2H 7QA |
20150034 | Little Venice | West Area Service Centre | 25498 | 09/04/2018 | null | 23963 | FLAT 36 Senior Street 34/80, Senior Street, Warwick Estate, London, W2 5TJ |
20149915 | Church Street | Central Area Service Centre | 118920 | 09/04/2018 | null | 16105 | FLAT 9 Stanfield House, Frampton Street, Lilestone Estate, London, NW8 8LE |
20149890 | Maida Vale | North Area Service Centre | 167754 | 02/04/2018 | null | 21193 | FLAT 1 Renfrew House, Carlton Vale, Maida Vale Estate, London, NW6 5ES |
20149836 | Lisson Green | Central Area Service Centre | 128140 | 02/04/2018 | null | 17131 | FLAT 3 Missenden House, Jerome Crescent, Lisson Green Estate, London, NW8 8SJ |
20149891 | Mozart & Avenue Gardens | North Area Service Centre | 172664 | 02/04/2018 | null | 12677 | FLAT 42 Birch House, Droop Street, London, W10 4EQ |
I have a Date calendar table that has the calendar dates.
What I need to achieve is if I can create a measure, I need to filter the date from Calendar table which will be linked to the SQL table above on the basis of comm_dat column, where if I select a date from Date Calendar table, it should filter out the data based on the below filter
Please note the date will be the date that will be automatically selected from Date Calendar Date Column.
comm_dat <= '2018-04-15'
AND
term_dat > '2018-04-15'
)
OR
(
comm_dat <= '2018-04-15'
AND
term_dat Is Null
Similary I have another table with same sort of values & these SQL tables, the new table will be linked to the date calendar table. This new table SQL Test 1 will to be linked with the date column from Date Calendar but it will have a different criteria of filteration
Again here the date will be the same date selected in Date from Date Calendar
where ( "end-dte" Is Null
OR
"end-dte" >= '2018-04-15'
user-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 |
I tried creating a parameter with the values but it did not help in any way. Is there a way this can be achieved? You help is highly appreciated on this as the entire report dependency is on this calculation.
Regards,
Prathamesh Sable
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
User | Count |
---|---|
140 | |
113 | |
104 | |
73 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |