Forum Discussion

ArchStanton's avatar
ArchStanton
Icon for Power Participant rankPower Participant
3 years ago
Solved

New Measure Required for Min & Max Values in Table

Hi,

 

I'm struggling to create 2 measures that I will then use to create a 3rd final measure which will subtract one from the other

 

Measure 1 = SUM of Actual Unassigned + Actual Assigned on MIN Date (Mar 2022)

Measure 2 = SUM of Actual Unassigned + Actual Assigned on MAX or Latest Date

Measure 3 = Measure 2 - Measure 1

 

Can anyone show me what I need to do please?

Thanks

Need Help
Show and Tell
  • Idrissshatila's avatar
    Idrissshatila
    3 years ago
    New Measure = 
     VAR _MinDate = MIN('Slide10 Caseload'[Month])
     VAR _MaxDate = Calculate(MAX('Slide10 Caseload'[Month],'Slide10 Caseload'[Actual Assigned] <> Blank())
     VAR _ActualStart = CALCULATE(
                 SUM('Slide10 Caseload'[Actual Assigned]),'Slide10 Caseload'[Month] =_MinDate)
    VAR _CurrentValues = CALCULATE(
              SUM('Slide10 Caseload'[Actual Unassigned]),'Slide10 Caseload'[Month] =_MaxDate)
    VAR _Result = _CurrentValues - _ActualStart

        RETURN
        _Result

     

    Try this now

7 Replies

  • Idrissshatila's avatar
    Idrissshatila
    Icon for Super User rankSuper User
    3 years ago

    Hello ,

    Try this


    Measure =

    Var MinDate = min(Month)

    Var MaxDate = Max(Month)
    Var measure1 = calculate(sum(Actual Unassigned),MinDate)
    Var measure2 = calculate(sum(Actual Unassigned),MaxDate)
    Var Result = measure2 - measure1
    Return
    Result

     

    If I answered your question, please mark my post as solution, Appreciate your Kudos!

    Follow me on Linkedin

    • ArchStanton's avatar
      ArchStanton
      Icon for Power Participant rankPower Participant
      3 years ago

      Hi,

      It didn't work. 

           
      The True/False expression does not specify a column. Each True/False expressions used as a table filter expression must refere exactly to one column

       

      New Measure = 
     VAR _MinDate = MIN('Slide10 Caseload'[Month])
     VAR _MaxDate = MAX('Slide10 Caseload'[Month])
     VAR _ActualStart = CALCULATE(
                        SUM('Slide10 Caseload'[Actual Assigned]),_MinDate)
    VAR _CurrentValues = CALCULATE(
                        SUM('Slide10 Caseload'[Actual Unassigned]),_MaxDate)
    VAR _Result = _CurrentValues - _ActualStart

        RETURN
        _Result

       

      I don't mind having just the _Actual Start & _CurrentValues measures, I prefer to create the 3rd one separately

       

      Thanks,

      • Idrissshatila's avatar
        Idrissshatila
        Icon for Super User rankSuper User
        3 years ago

         

        New Measure = 
     VAR _MinDate = MIN('Slide10 Caseload'[Month])
     VAR _MaxDate = MAX('Slide10 Caseload'[Month])
     VAR _ActualStart = CALCULATE(
                 SUM('Slide10 Caseload'[Actual Assigned]),'Slide10 Caseload'[Month] =_MinDate)
    VAR _CurrentValues = CALCULATE(
              SUM('Slide10 Caseload'[Actual Unassigned]),'Slide10 Caseload'[Month] =_MaxDate)
    VAR _Result = _CurrentValues - _ActualStart

        RETURN
        _Result

         

        Try it now