Forum Discussion

Zaheer21's avatar
Zaheer21
Frequent Visitor
1 year ago
Solved

YTD Measure using Month Name Slicer

Hi PBI Community,

I'm stuck with the following issue.

I have a table in Power BI displaying values as shown below.

 

In Power BI, I want to create a YTD measure that sums the values of the Current_Month column based on the selected month.

For example, if the user selects Year = 2025 and MonthName = February, the measure should sum the values from January till February.

While using MonthNumber in the slicer it  works correctly, using MonthName only filters data for the selected month instead of calculating the YTD sum.

can someone help me to get YTD value if monhname is put in the slicer.



 

Below is my measure 

YTD_Current_Month =
VAR SelectedYear = SELECTEDVALUE(Bassria[Year])
VAR SelectedMonth = MAX(Bassria[Month_Number])

RETURN
CALCULATE(
    SUM(Bassria[Current_Month]),
        Bassria[Category] = "Actual" &&
        Bassria[Year] = SelectedYear &&
        Bassria[Month_Number] <= SelectedMonth
)


looking forward to hear from community

Need Help
  • ArwaAldoud's avatar
    ArwaAldoud
    1 year ago

    Hi Zaheer21, 

    Try sorting the Month_Name column by Month_Number in the dataset to ensure correct chronological order.

    Updated YTD Measure:

    YTD_Current_Month =
    VAR SelectedYear = SELECTEDVALUE(Bassria[Year])
    VAR SelectedMonthName = SELECTEDVALUE(Bassria[Month_Name])
    VAR SelectedMonthNumber =
    CALCULATE(
    MAX(Bassria[Month_Number]),
    Bassria[Month_Name] = SelectedMonthName
    )

    RETURN
    CALCULATE(
    SUM(Bassria[Current_Month]),
    Bassria[Category] = "Actual",
    Bassria[Year] = SelectedYear,
    Bassria[Month_Number] <= SelectedMonthNumber,
    ALL(Bassria[Month_Name]) -- Ensures we don't filter out earlier months
    )

     

    I have attached the updated .pbix file for reference.

    If this response was helpful, please accept it as a solution and give kudos to support other community members. 

     

5 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    1 year ago

    Hi,

    Try this approach

    1. Created a calculated column (Date) formula = 1*("1/"&Data[Month_number]&"/"&Data[Year])
    2. Create a Calendar table with calculated column formulas for Year, Month name and Month number.  Sort the Month name column by the Month number
    3. Create a relationship (Many to One and Singe) from the Date column of the Fact table to the date column of the Calendar table
    4. To your visual/slicer/filter, dray Year and Month name from the Calendar table and select a month/year
    5. Write these measures

    Total = sum(Data[Sales])

    Total YTD = calculate([total],datesytd(calendar[date])

    Hope this helps.

  • bhanu_gautam's avatar
    bhanu_gautam
    Super User
    1 year ago

    Zaheer21 , Try using

    DAX
    YTD_Current_Month =
    VAR SelectedYear = SELECTEDVALUE(Bassria[Year])
    VAR SelectedMonthName = SELECTEDVALUE(Bassria[Month_Name])
    VAR SelectedMonthNumber =
    CALCULATE(
    MAX(Bassria[Month_Number]),
    Bassria[Month_Name] = SelectedMonthName
    )

    RETURN
    CALCULATE(
    SUM(Bassria[Current_Month]),
    Bassria[Category] = "Actual" &&
    Bassria[Year] = SelectedYear &&
    Bassria[Month_Number] <= SelectedMonthNumber
    )

  • ArwaAldoud's avatar
    ArwaAldoud
    Super User
    1 year ago

    Hi Zaheer21, 

    Try sorting the Month_Name column by Month_Number in the dataset to ensure correct chronological order.

    Updated YTD Measure:

    YTD_Current_Month =
    VAR SelectedYear = SELECTEDVALUE(Bassria[Year])
    VAR SelectedMonthName = SELECTEDVALUE(Bassria[Month_Name])
    VAR SelectedMonthNumber =
    CALCULATE(
    MAX(Bassria[Month_Number]),
    Bassria[Month_Name] = SelectedMonthName
    )

    RETURN
    CALCULATE(
    SUM(Bassria[Current_Month]),
    Bassria[Category] = "Actual",
    Bassria[Year] = SelectedYear,
    Bassria[Month_Number] <= SelectedMonthNumber,
    ALL(Bassria[Month_Name]) -- Ensures we don't filter out earlier months
    )

     

    I have attached the updated .pbix file for reference.

    If this response was helpful, please accept it as a solution and give kudos to support other community members.