Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Running Total by OrderFactTable

I have a OrderFactTable that has an OrderDate.  I need to calculate a RunningTotal on a Patient Level for 45 days when a certain trigger happens on that patient.  In this instance the Trigger would be when it has been over 90 days since their previous order.  

 

 

So their is an OrderFact linked to a DateDim and a PatientDim.  I need a running total on a Numeric column 'TotalMME' for 45 days after the Date the Patient has had their first Order in atleast 90 day.

 

Thanks

Need Help

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Anonymous , You can get running total like

     

    calculate(Sum(OrderFact[TotalMME]), filter(all(Date), Date[Date] <= max(Date[Date]) ) )

     

    or

     

    calculate(Sum(OrderFact[TotalMME]), filter(allselected(Date), Date[Date] <= max(Date[Date]) ) )

     

    Not clear on 45 and 90 logic

     

    If this does not help
    Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Here is the .pbix  sample 

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Hi amitchandak , any thoughts on the sample .pbix i posted?