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.

Anonymous's avatar
Anonymous
Not applicable
1 year ago

Hi WaninGNighT 

 

Please make sure you use the Month field in the Dim_Date table as the X-axis in the column chart instead of the Month field in the Slicer_Date table.
I did a simple test based on your description and can get what you want using your measure. For details, please see my pbix file.

Total Sales Amount = 
CALCULATE(
    SUM(Sales[Sales_Amount]),
    DATESYTD(Dim_Date[Date])
)
YTD = 
VAR M =
    SELECTEDVALUE ( 'Slicer_Date'[Month] )
VAR Y =
    SELECTEDVALUE ( 'Slicer_Date'[Year] )
RETURN
        CALCULATE (
            [Total Sales Amount],
            FILTER (
                ALL ( 'Dim_Date' ),
                'Dim_Date'[Year] = Y
                    && 'Dim_Date'[Month] <= M
                    && 'Dim_Date'[Month] <= MAX ( 'Dim_Date'[Month] )
            )
        )

 

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.

 

WaninGNighT's avatar
WaninGNighT
Frequent Visitor
1 year ago

Thank you Anonymous, I also sent you a private message asking further questions.

I tried your method but it didn't quite work for me.

I'm actually trying to do the same thing as this video shows: https://www.youtube.com/watch?v=DroZHtYA1-E but I stucked at the first step.

I used exactly the same measure, and also created the toggle button:

and if I choose September like the video, it only shows me one bar, which make me really confused: 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    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.

    • WaninGNighT's avatar
      WaninGNighT
      Frequent Visitor
      1 year ago

      Thank you so much Anonymous . Although I'm still a little bit confused, because in the video I mentioned, before he introduced the second date table, the column chart is still able to show all the previous months of the selected month from the slicer. However, I'll let it go and keep going on my dashboard. If I have further questions, I'll probably start another post and hope you can answer me more questions at that time!