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 o...
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. 

     

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.