none
How to handle Expected and Actual dates based calculations in DAX ?

    Question

  • Hi,

    We are having a machine provisioning system and we are having two columns in that. Requested Provisioning Date & Actual Provisioning Date. We need to track performance with respect to Requested Provisioning month.

    1. Irrespective of requested Provisioning date, whatever machine provisioned on a particular month is considered as "Provisioned".

    2. If actual machine provisioning month > requested provisioning month, then "Backlog"

    Whenever I apply filter on "RequestedDate Of Provisioning" only the requests for that month are considered. But, I want to consider all requests actually provisioned on that month(ActualProvisioningDate).

    I tried creating one additional table called "ActualProvisioningDate" , but it is not helping.

    Please throw some light on accomplishing the above scenario in DAX.

    Thanks in advance,

    Venkat


    Venkataraman R

    Monday, March 12, 2012 9:06 AM

Answers

  • Hi Javier,

    Yes. I was able to acheive this. What I did is, I ignored the RequestedDeliveryDate filter by applying ALL(ProvisionTable), and applied separate filter on ActualDeliveryDate as DATESBETWEEN(ActualDeliveryDate,STARTOFMONTH(FiscalDate[Date]),ENDOFMONTH(FiscalDate[Date])). Here FiscalDate is the DateTable which is having relationship with RequestedDeliveryDate.

    Thanks and Regards,

    Venkat


    Venkataraman R

    • Marked as answer by Venkataraman R Wednesday, March 14, 2012 5:11 AM
    Wednesday, March 14, 2012 5:11 AM

All replies

  • Hi Venkataraman

    You can usually achieve this kind of result by manipulating the context of the calculation, in this case the ActualProvisioningDate.   Can you post a sample of your data so it would be easier to understand what you are trying to do?




    Javier Guillen
    http://javierguillen.wordpress.com/

    Tuesday, March 13, 2012 2:30 AM
    Answerer
  • Hi Javier,

    Yes. I was able to acheive this. What I did is, I ignored the RequestedDeliveryDate filter by applying ALL(ProvisionTable), and applied separate filter on ActualDeliveryDate as DATESBETWEEN(ActualDeliveryDate,STARTOFMONTH(FiscalDate[Date]),ENDOFMONTH(FiscalDate[Date])). Here FiscalDate is the DateTable which is having relationship with RequestedDeliveryDate.

    Thanks and Regards,

    Venkat


    Venkataraman R

    • Marked as answer by Venkataraman R Wednesday, March 14, 2012 5:11 AM
    Wednesday, March 14, 2012 5:11 AM