Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago

DAX formula

Is it possible to adjust the following measure:  CurrentMonthRFTYTD = IF(     MONTH(MAX('analysis vwTime'[Date])) > MONTH(TODAY())     && YEAR(MAX('analysis vwTime'[Date])) >= YEAR(TODAY()),  ...
Need Help
bhanu_gautam's avatar
bhanu_gautam
Icon for Super User rankSuper User
1 year ago

Anonymous , Try using 

 

CurrentMonthRFTYTD =
VAR CurrentMonth = MONTH(TODAY())
VAR CurrentYear = YEAR(TODAY())
VAR MaxDate = MAX('analysis vwTime'[Date])
VAR MaxMonth = MONTH(MaxDate)
VAR MaxYear = YEAR(MaxDate)
RETURN
IF(
MaxYear > CurrentYear || (MaxYear = CurrentYear && MaxMonth > CurrentMonth),
BLANK(),
DIVIDE(
CALCULATE(
SUM('API RFT'[RFT]),
FILTER(
'analysis vwTime',
YEAR('analysis vwTime'[Date]) = CurrentYear &&
MONTH('analysis vwTime'[Date]) = CurrentMonth
)
),
CALCULATE(
COUNT('API RFT'[RFT]),
FILTER(
'analysis vwTime',
YEAR('analysis vwTime'[Date]) = CurrentYear &&
MONTH('analysis vwTime'[Date]) = CurrentMonth
)
)
) / 100
)

 

API RFT % Change MoM =
VAR CurrentMonthRFTYTD = [CurrentMonthRFTYTD]
VAR PreviousMonthRFTYTD = [PreviousMonthRFTYTD]
RETURN
IF(
ISBLANK(CurrentMonthRFTYTD) || ISBLANK(PreviousMonthRFTYTD),
BLANK(), -- Return BLANK instead of 0
CurrentMonthRFTYTD - PreviousMonthRFTYTD
)

 

If you still face any issue, please share sample data to validate

Anonymous's avatar
Anonymous
Not applicable
1 year ago

Hi bhanu_gautam. Thanks for your response.

The formula is unfortunately still not working as intended. I forgot to mentin, but the measure should still show the percentage as a YTD but for the currenth month only. So maybe something like this still? 

  TOTALYTD(SUM('API RFT'[RFT]), 'analysis vwTime'[Date]),
        TOTALYTD(COUNT('API RFT'[RFT]), 'analysis vwTime'[Date]).
But i still can't get it to show me the % for the current month without actively setting a date filter.