Forum Discussion

WaninGNighT's avatar
WaninGNighT
Frequent Visitor
1 year ago
Solved

Dynamic YTD with Month slicer

Hi all,   I'm tring to create a column chart showing cumulative YTD Sales by month. My data include a Sales table, a Dim_Date table related to the Sales table, and a disconnected Slicer_Date table ...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi WaninGNighT 

     

    Thanks for your pbix file, I can't reply to private messages due to policy reasons, please refer to the solution below, I hope it helps.

    1. You should create the slicer using the 'Year' and 'Month' fields from the disconnected date table_'Slicer Date', rather than the fields in the 'Dim Date' table.
    2. Create the following measures:

    YTD/QTD/MTD = SWITCH(
    SELECTEDVALUE('Time Intelligence'[Period]),
    "YTD", CALCULATE([Total Sales Amount], DATESYTD(Dim_Date[Date])),
    "QTD", CALCULATE([Total Sales Amount], DATESQTD(Dim_Date[Date])),
    "MTD", CALCULATE([Total Sales Amount], DATESMTD(Dim_Date[Date]))
)
    New YTD/QTD/MTD = 
VAR SelectedYear = SELECTEDVALUE(Slicer_Date[Year])
VAR SelectedMonth = SELECTEDVALUE(Slicer_Date[Month])
RETURN
CALCULATE(
    [YTD/QTD/MTD],
    FILTER(
        Dim_Date,
        Dim_Date[Year] = SelectedYear &&
        Dim_Date[Month] <= SelectedMonth
        )
)


    3. Place the 'Month' or 'Month Name' field from the 'Dim Date' table and the measure 'New YTD/QTD/MTD' into the column chart.

     

     

    Here are my test results:

     

     

    Best Regards,
    Jarvis Tang
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

anilelmastasi's avatar
anilelmastasi
Icon for Super User rankSuper User
1 year ago

Hello WaninGNighT ,

 

Your current YTD measure is not working as expected because of how you are filtering Dim_Date. The issue arises from the fact that when you select a month, it only returns data for that single month instead of accumulating values up to that month.

 

YTD Sales =
VAR SelectedYear = SELECTEDVALUE ( 'Slicer_Date'[Year] )
VAR SelectedMonth = SELECTEDVALUE ( 'Slicer_Date'[Month] )

RETURN
CALCULATE (
[Total Sales Amount], -- Your total sales measure
FILTER (
ALL ( 'Dim_Date' ), -- Ensures all dates are considered
'Dim_Date'[Year] = SelectedYear &&
'Dim_Date'[Month] <= SelectedMonth
)
)

 

 

Previously, you were using MAX ( 'Dim_Date'[Month] ), which caused the measure to only return values for the selected month. Now, we make sure to include all months up to the selected month.

 

ALL ( 'Dim_Date' ) ensures that the measure evaluates all months in the selected year, rather than just those currently selected in the visual.

 

If this answer helpful for you, select as an answer and kudos please.

Thank you!