Forum Discussion

Avivek's avatar
Avivek
Icon for Post Partisan rankPost Partisan
5 years ago

Need help with the DAX

I am trying to create a measure with the following logic, If last 1 completed month and the current month is selected then show the Total Exp. Usage Projected for only current month, if last 2 month...
Need Help
Avivek's avatar
Avivek
Icon for Post Partisan rankPost Partisan
5 years ago

Pragati11 

I am using an existing  measure 

Tot. Act. proj. Mon. Usage = [Act. proj. Mon. Usage]+[Act. Last2 Mon Comp.Usage]

 where 

Act. proj. Mon. Usage = ([Act. proj. Usage]/max('DO Vs Tag'[Completed Bus Days]) *max('DO Vs Tag'[Cur Month No of Bus Days]))

Act. Last2 Mon Comp.Usage = CALCULATE(count('DO Vs Tag'[Procedure Credit Owner]), FILTER('DO Vs Tag','DO Vs Tag'[Last 2 Mon Procedure]=1))

The 1st part of the measure that is Act. proj. Mon. Usage will remain as it is. I need to change Act. Last2 Mon Comp.Usage toAct. Last N Mon Comp.Usage where last N month should show the following logics;

  1. When current month and the last completed month is selected then it should show only for the current month. For eg- When Sep and Oct are selected then show only Oct projection as Tot.Proj
  2. When current month and the last 2 months are selected then it should show the current month and the last completed month, Eg.,Aug, Sep and Oct are selected then show Sep+Oct as tot proj.
  3. When current month and last 3 months are selected then it should display Current month and the last 2 completed months. For eg If July, Aug, Sep and Oct is selected, show Aug+Sep+Oct as tot proj.
Pragati11's avatar
Pragati11
Icon for Super User rankSuper User
5 years ago

Hi Avivek ,

 

Share some input data and the expected output as a sample so that it can be tried at our end.

 

Thanks,

Pragati

  • Avivek's avatar
    Avivek
    Icon for Post Partisan rankPost Partisan
    5 years ago

    Pragati11 ,

    I tried to illustrate an example, please let me know if this helps

     

    • v-deddai1-msft's avatar
      v-deddai1-msft
      Icon for Community Support rankCommunity Support
      5 years ago

      Hi Avivek ,

       

      It can be done, but we really need your sample data for reference. Would you please inform us more detailed information( your sample data and expected output (by OneDrive for Business)) if possible? Then we will help you more correctly.

       

      Please do mask sensitive data before uploading.

       

      Thanks for your understanding and support.

       

      Best Regards,

      Dedmon Dai