Forum Discussion

cathyPB's avatar
cathyPB
Frequent Visitor
6 years ago
Solved

IF statement with Calculated Measure

 I would like to create a new measure with some value like this:

IF  Before today:

CALCULATE(
 SUM('DataMart'[ACTUAL]),
 FILTER(
  ALLSELECTED('DIM_DATE'[DAY_DATE]),
  ISONORAFTER('DIM_DATE'[DAY_DATE], max(DIM_DATE[DAY_DATE]), DESC)))

If it After today: 0

 

Any suggestions?

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    cathyPB - Does this measure work?:

     

    Your Measure 2 = 
var _Date_As_Of = MAX('Dim_Date'[Day_Date])
return CALCULATE(
    SUM('DataMart'[ACTUAL]),
    FILTER(
        ALL('Dim_Date'),
        AND(_Date_As_Of < TODAY(), 'Dim_Date'[Day_Date] <= _Date_As_Of)
    )
)

     

     

10 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    cathyPB - Could you please clarify: are you wanting to compare the fact table date vs today's date and return 0 if it's in the future?

    • cathyPB's avatar
      cathyPB
      Frequent Visitor
      6 years ago

      Yes, I do have Date dimension as well.

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        cathyPB  - Check whether this Measure works:

        Your Measure = 
CALCULATE(
    SUM('DataMart'[ACTUAL]),
    FILTER(
        'Dim_Date',
        'Dim_Date'[Day_Date] < TODAY()
    )
)
        I hope this helps. If it does, please Mark as a solution.
        I also appreciate Kudos.