Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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_numarea_dsc3area_dsc4numcomm_datterm_datpr_seq_nofull_addr
20150122St Johns WoodCentral Area Service Centre6444809/04/2018null24563FLAT 19 Ashby Court, Pollitt Drive, Wharncliffe Gardens Estate, London, NW8 8UA
20149968Mozart & Avenue GardensNorth Area Service Centre17629209/04/2018null2195732 Parry Road 40/60, Parry Road, Mozart Estate, London, W10 4SZ
20150032GrosvenorSouth Area Service Centre248409/04/2018null4356FLAT 31 Princess Mary House, Vincent Street, Grosvenor Estate, London, SW1P 4HL
20149920Soho and Covent GardenSouth Area Service Centre11349609/04/2018null18870FLAT 5 Greens Court, 4 Greens Court, London, W1F 0HE
20150076Soho and Covent GardenSouth Area Service Centre635709/04/2018null19544FLAT 106 Vale Royal House, 36 Newport Court, London, WC2H 7QA
20150034Little VeniceWest Area Service Centre2549809/04/2018null23963FLAT 36 Senior Street 34/80, Senior Street, Warwick Estate, London, W2 5TJ
20149915Church StreetCentral Area Service Centre11892009/04/2018null16105FLAT 9 Stanfield House, Frampton Street, Lilestone Estate, London, NW8 8LE
20149890Maida ValeNorth Area Service Centre16775402/04/2018null21193FLAT 1 Renfrew House, Carlton Vale, Maida Vale Estate, London, NW6 5ES
20149836Lisson GreenCentral Area Service Centre12814002/04/2018null17131FLAT 3 Missenden House, Jerome Crescent, Lisson Green Estate, London, NW8 8SJ
20149891Mozart & Avenue GardensNorth Area Service Centre17266402/04/2018null12677FLAT 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-cdetrn-valend-dteocc_numarea_dsc3area_dsc4numcomm_datterm_datpr_seq_nofull_addr
278098X      19.7615/04/201820045216aSouth Area Service Centre449316/02/1987null20423FLAT 103 Semley House, Semley Place, London, SW1W 9QL
3110315      109.2815/04/201820146368dNorth Area Service Centre16701128/08/2017null23627FLAT 56 Burlington Close, Burlington Close, Walterton Road Estate, London, W9 3LZ
2057509      12.3315/04/201820054393xCentral Area Service Centre854318/02/2002null13652FLAT 35 Lambourne House, Broadley Street, Church Street Estate, London, NW8 8DN
2797448      143.5915/04/201820047918cSouth Area Service Centre562319/07/2010null16468FLAT 20 Stourhead House, Tachbrook Street, Lillington Gardens Estate, London, SW1V 2QE
2277128      13.5315/04/201820063051cWest Area Service Centre800211/07/2005null15747FLAT 17 Bridgewater House, Hallfield Estate, Hallfield Estate, London, W2 6EE
2004678      1.0515/04/201820045719cSouth Area Service Centre477931/05/1999null18076FLAT 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

4 REPLIES 4
Greg_Deckler
Super User
Super User

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()))

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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)

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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-cdetrn-valend-dteocc_numarea_dsc3area_dsc4numcomm_datterm_datpr_seq_nofull_addr
278098X      19.7615/04/201820045216aSouth Area Service Centre449316/02/1987null20423FLAT 103 Semley House, Semley Place, London, SW1W 9QL
3110315      109.2815/04/201820146368dNorth Area Service Centre16701128/08/2017null23627FLAT 56 Burlington Close, Burlington Close, Walterton Road Estate, London, W9 3LZ
2057509      12.3315/04/201820054393xCentral Area Service Centre854318/02/2002null13652FLAT 35 Lambourne House, Broadley Street, Church Street Estate, London, NW8 8DN
2797448      143.5915/04/201820047918cSouth Area Service Centre562319/07/2010null16468FLAT 20 Stourhead House, Tachbrook Street, Lillington Gardens Estate, London, SW1V 2QE
2277128      13.5315/04/201820063051cWest Area Service Centre800211/07/2005null15747FLAT 17 Bridgewater House, Hallfield Estate, Hallfield Estate, London, W2 6EE
2004678      1.0515/04/201820045719cSouth Area Service Centre477931/05/1999null18076FLAT 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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.