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()),
    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.
Need Help

5 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Super User
    1 year ago

    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
    )

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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's avatar
      bhanu_gautam
      Super 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. 
  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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.