Forum Discussion

dljdavid's avatar
dljdavid
Frequent Visitor
6 years ago

Measure from two fact tables depending on a date

Hello there!

Hope somebody can help me with this 🙂

 

I have 2 fact tables:

 

Foreseen production table:

DateReservation IDQuantityAmount
01/01/2020101200
01/01/2020111100
02/01/2020121120
03/01/2020131100
04/01/2020141100
05/02/2020151100

 

Production done table:

DateReservation IDQuantityAmount
01/01/2020101200
01/01/2020111100
02/01/2020121120

 

Imagine today is 03/01/2020

Foreseen production table can have records for all dates (past and future).

Production done table only can have records for past dates, as is production already done. 

 

I want a measure that calculates the total sum of quantity and amount, but filtered on the date selected, like this:

 

So if I select the date 03/01/2020 I should get:

DateReservation IDQuantityAmountTable
01/01/2020101200Produced
01/01/2020111100Produced
02/01/2020121120Produced
03/01/2020131100Foreseen
04/01/2020141100Foreseen
05/02/2020151100Foreseen

 

I need to discard the records in the foreseen as the date is already produced.

 

I hope my example is clear, thanks in advance for your help!

 

David

 

 

Need Help

3 Replies

  • Stachu's avatar
    Stachu
    Icon for Community Champion rankCommunity Champion
    6 years ago

    the easiest way would be to append the 2 tables to something like this:

    DateReservation IDQuantityAmountType

    01 January 2020 10 1 200 Foreseen
    01 January 2020 11 1 100 Foreseen
    02 January 2020 12 1 120 Foreseen
    03 January 2020 13 1 100 Foreseen
    04 January 2020 14 1 100 Foreseen
    05 February 2020 15 1 100 Foreseen
    01 January 2020 10 1 200 Done
    01 January 2020 11 1 100 Done
    02 January 2020 12 1 120 Done

    but it can be achieved with your setup as well, as long as you add Calendar and Reservation Id tables, joined with both fact tables:

    you can then use:

    SelectedDate = SELECTEDVALUE('Calendar'[Date])

    and

    Amount = 
VAR __SelectedDate = [SelectedDate]
VAR __ForeseenQuantity = CALCULATE(SUM('Foreseen production'[Amount]),'Calendar'[Date]>=__SelectedDate)
VAR __DoneQuantity = CALCULATE(SUM('Production done'[Amount]),'Calendar'[Date]<__SelectedDate)
RETURN
__ForeseenQuantity+__DoneQuantity

    which gives:

    The Reservation Id and Date are coming from dimension tables, not fact tables

    • dljdavid's avatar
      dljdavid
      Frequent Visitor
      6 years ago

      Thank you Stachu 

      It works as described!

      But I would like to add a little bit more of complexity to my example, sorry if I'm "too newbie" 🙂

       

      So we have a selected date, but I want to see the amounts produced for a month inside a year, for an example like this:

       

      DateReservation IDAmountQuantityType
      31/12/201991001Produced
      01/01/2020101001Produced

       

      DateReservation IDAmountQuantityType
      31/12/201991001Foreseen
      01/01/2020101001Foreseen
      02/01/2020111001Foreseen
      01/02/2020121001Foreseen

       

      So if I select the date of 02/01/20 I would like to see this for year 2020:

       

      Month/yearAmountQuantity
      January  2020200 (100 produced + 100 foreseen)2
      February 2020100 (foreseen)1

       

      Please note that year 2019 is discarded, as I want to see only 2020.

       

      Thanks everybody for your time and help, I really appreciate it!

       

      David

  • v-xicai's avatar
    v-xicai
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi dljdavid ,

     

    Firstly, you may create relationship between the two tables above, then create column like DAX below.

     

     

    Table= IF('Foreseen production'[Date]=RELATED('Production'[Date]), "Produced", "Foreseen")

     

     

    Secondly, create a calendar table without relationship with your fact data table, and let the Calendar[Date] as the source of Slicer visual.

     

     

    Calendar= CALENDARAUTO()

     

     

    Finally, you can create measure Filter1 like DAX below, put the Filter1 in the Visual Level Filter of Table visual which is displaying all fields of table 'Foreseen production' , setting Filter1 as "is not blank". This way to discard the produced records in the foreseen table by changing Date slicer.

     

     

    Filter1 =IF('Foreseen production'[Date]> SELECTEDVALUE(Calendar[Date]) , 1, BLANK())

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

    Best Regards,

    Amy 

     

    Community Support Team _ Amy

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.