Forum Discussion
DAX formula
Hi bhanu_gautam. Thanks for your response and help. I tried the formula, but it doesn't seem to work correctly.
1. It doesn't give me the right data from my API RFT for the current month. The previous formula did.
2. It still shows (blank) without me setting a date filter for the current month.
3. Data is not registered for all the previous months where there is API RFT data. The previous formula did.
I hope this feedback is useful, and thank you so much for your help!!
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
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.
