IF statement with Calculated Measure

[cathyPB]

 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?

[Anonymous]

@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]

Yes, I do have Date dimension as well.

[Anonymous]

@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.

[cathyPB]

Thanks for reply, but I would like to get accumulative sum result as following:

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

[Anonymous · Accepted solution]

@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)
    )
)

[Anonymous]

Hello,

I am also having a similar issue. I am looking to do something like this. IF week = 1 and timesheet issue = missing  then missing if week = 1 and timesheet = good then sum actual hours. I want the results to populate in the same column. Any suggestions?

[Anonymous]

@Anonymous - Please post a new thread, and refer to this blog post for guidance.

[cathyPB]

Not yet, it only return one value as the point in the chart

[Anonymous]

@cathyPB  - Could you please mock up what you are looking for and provide a screenshot of what is currently happening?

[cathyPB]

@Anonymous   It works. The dot comes from the higher level, so drill down to lower level does work.Thanks.