DAX formula

[Anonymous]

Is it possible to adjust the following measure: 

CurrentMonthRFTYTD =

IF(

    MONTH(MAX('analysis vwTime'[Date])) > MONTH(TODAY())

    && YEAR(MAX('analysis vwTime'[Date])) >= YEAR(TODAY()),

    BLANK(),

    DIVIDE(

        TOTALYTD(SUM('API RFT'[RFT]), 'analysis vwTime'[Date]),

        TOTALYTD(COUNT('API RFT'[RFT]), 'analysis vwTime'[Date])

    ) / 100

)
so it can show the CurrentMonth API RFT on a card visual without me having to set a date filter for the specific month? It should automatically update to take the data from API RFT, even though 11 days has only gone by in this month for example. Right now it just says (blank) unless I choose october 2024. In tabel visual it shows the data correctly, showing the % of API RFT for each month when data is registered. I have experienced with some different things and I got it to show the correct data for the current month, but then it also added 0% for future months, which didn't look nice in my table visual. If no data is registered in future months (which there never will be), it should not show anything. I hope this makes sense! Thanks in advance. 

It is essentially so this measure: 

API RFT % Change MoM =

VAR CurrentMonthRFTYTD = [CurrentMonthRFTYTD]

VAR PreviousMonthRFTYTD = [PreviousMonthRFTYTD]

RETURN

    IF(

        ISBLANK(CurrentMonthRFTYTD) || ISBLANK(PreviousMonthRFTYTD),

        BLANK(), -- Return BLANK instead of 0

        CurrentMonthRFTYTD - PreviousMonthRFTYTD

    )
always shows data for the current month without me having to set a date filter.

[Anonymous]

Hi @Anonymous ,

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.

Best Regards,

Clara Gong

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

[Anonymous]

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!!

[bhanu_gautam]

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

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. 

[bhanu_gautam]

@Anonymous , you can modify the measure to dynamically calculate the value for the current month. 

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(
MaxMonth > CurrentMonth && MaxYear >= CurrentYear,
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
)

For your API RFT % Change MoM measure, you can keep it as is, since it relies on the CurrentMonthRFTYTD measure to provide the current month's data.

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