Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Help With IF Sum

Hi,

 

I am trying to recreate the following calculation into a DAX Measure. Please can someone advice how ths is possible:-

 

    if  {Job_Mth}<={Current Month (Year Month YYYY-MMM Selection from Slicer} then

         {Job Projected Pounds}

    else

         {Job Contract Amount}

 

Thanks in advance

Need Help

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Anonymous , If you have job_date it would much more easy

    measure

    = if(max(Table[Job Date]) <= eomonth(Today(),0)  ,[Job Projected Pounds], [Job Contract Amount])

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi<

     

    Thanks again.

     

    Ok so i have a date in thefuture in my projmnth field. When we use MAX in the measure its taking this into consideration which i want to ignore.

     

    Any proj months after the current period selected need to sum the contract amount anything less than or equal to the current period needs to sum proj dollars.

     

    On the example below i wish to get the sum of proj dollars (12,210.130.00) the measure is returning the other value

     

  • v-kkf-msft's avatar
    v-kkf-msft
    Community Support
    4 years ago

    Hi Anonymous ,

     

    I am not sure if I understood your question correctly. If you use this formula, 

    IF_Measure = 
IF (
    MAX ( 'Table'[ProjMth] ) <= EOMONTH ( TODAY (), 0 ),
    SUM ( 'Table'[ProjDollars] ),
    SUM ( 'Table'[ContractAmt] )
)

    because 12/1/2050 > 4/30/2022, then return SUM ( 'Table'[ContractAmt] ), i.e. 12182946.

     

    If you want to calculate the sum of column [ProjDollars] less than or equal to the current month, please try:

     

    less_equal_currentperiod = 
CALCULATE (
    SUM ( 'Table'[ProjDollars] ),
    FILTER ( 'Table', 'Table'[ProjMth] <= EOMONTH ( TODAY (), 0 ) )
)

     

    If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
    Best Regards,
    Winniz
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.