Measure with IF statement in DATE

[nbidark]

I am new to this community and looking for help with a measure having IF statement using dates.

I have fiscal year to date Operating Revenue, but I would like to calculate monthly operating revenue, so I used the parallel period function. 

Parallel Period Operating Revenue = CALCULATE(SUM('Revenue Table'[Total Operating Revenue]), PARALLELPERIOD('Revenue Table'[As of Date], -1, MONTH) )

Monthly Operating Revenue = [Total Operating Revenue] - [Parallel Period Operating Revenue].

My problem - The fiscal year is January to December. I do not want this formula to do the calculation for month of January, because January is the first month.  I want the calculation to apply from Feb.

I want to acheive (writing in simple english) - IF ( Month = "January", [Total Operating Revenue] Else [Monthly Operating Revenue]. 

Please help me write an appropriate measure.

Sample Data

As of Date	Product	Operating Revenue
1/31/2020	A	50
2/29/2020	A	65
3/31/2020	A	72
4/30/2020	A	94
5/31/2020	A	105
6/30/2020	A	118.9
7/31/2020	A	132.8
8/31/2020	A	146.7
9/30/2020	A	160.6
10/31/2020	A	174.5
11/30/2020	A	188.4
12/31/2020	A	202.3
1/31/2021	A	32
2/28/2021	A	45
3/31/2021	A	63
4/30/2021	A	74
5/31/2021	A	89.5
6/30/2021	A	103.9
7/31/2021	A	118.3
8/31/2021	A	132.7
9/30/2021	A	147.1
10/31/2021	A	161.5

Thank you very much.

[v-stephen-msft]

Hi @nbidark ,

Here's my solution.

1.Create a calendar table.

Calendar =
ADDCOLUMNS (
    CALENDARAUTO ( 1 ),
    "YearMonth", FORMAT ( [Date], "YYYY-MMM" ),
    "Year", YEAR ( [Date] )
)

The relationship is as follows.

2.Create a measure.

Revenue =
IF (
    MONTH ( MAX ( 'Calendar'[Date] ) ) = 1,
    CALCULATE (
        SUM ( 'Table'[Operating Revenue ] ),
        FILTER ( ALLSELECTED ( 'Calendar' ), [Year] = MAX ( 'Calendar'[Year] ) )
    ),
    SUM ( 'Table'[Operating Revenue ] )
)

3.Results.

You can check more details from the attachment.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[nbidark]

Thank you very much for the detailed answer. Really appreciate your time.

[YukiK · Accepted solution]

You can use a measure like this:

Monthly Operating Revenue =
VAR __SelectedMonth = SELECTEDVALUE( DateDim[Month] )
VAR __MonthlyOperRev = 
IF ( __SelectedMonth  = "January", "Whatever you want in case of January", [Total Operating Revenue]-[Monthly Operating Revenue],   )
RETURN
__MonthlyOperRev 

Please give it a thumbs up if this helps!

[amitchandak]

@nbidark , You should use date table for time intelligence and date table should be marked as date table

Parallel Period Operating Revenue = CALCULATE(SUM('Revenue Table'[Total Operating Revenue]), PARALLELPERIOD('Date'[Date], -1, MONTH) )

or

Parallel Period Operating Revenue = CALCULATE(SUM('Revenue Table'[Total Operating Revenue]), dateadd('Date'[Date], -1, MONTH) )

[nbidark]

Thank you for replying. I understand the parallel period part, I am stuck with the part where I want to avoid the calculation for month of January. Can you help me with writing a measure for that. I have mentioned it in the original post.