Forum Discussion
Measure with IF statement in DATE
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,...
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 __MonthlyOperRevPlease give it a thumbs up if this helps!
Hi Anonymous ,
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.
Thank you very much for the detailed answer. Really appreciate your time.