MTD Chart Visual

[Anonymous]

Hello,

I need help with the following problem.
I created 4 tables in Excel for the example. The Data and Sales table is connected via date with the Date Table.
I need to create a Chart graph visualization that will work like the image below.
I want to create a Slicer visualization in which I will put the month from the Date Table. I also created a Source Table in which the Data Act/Sales Act is calculated.

However, the problem arises when I want this column or data metric in the Chart Graph. I don't want to see only one value for a given month when I select a month in Slicer, as it usually does when I select a month in Slicer, but I want to see cumulatively everything from the first month to the one I selected. So, for example, when selecting month number 3, I want to see the amount for month 1, 2 and 3 in the Chart graph as I drew in the picture.

Can someone help me with this? Thank you 🙂

[Anonymous]

Hi @Anonymous ,

I created a sample pbix file(see the attachment), please check if that is what you want.

1. Create a month dimension table and apply the month field on the slicer

Months = VALUES('Date Table'[Month])

2. Create a measure as below 

Measure = 
VAR _selmonth =
    SELECTEDVALUE ( 'Months'[Month] )
RETURN
    SUMX (
        FILTER ( 'Date Table', 'Date Table'[Month] <= _selmonth ),
        [Data Act/Sales Act]
    )

3. Create a visual as below screenshot

Best Regards

[Anonymous]

Hiii 🙂

Thank you very much for providing the solution. However, I have a couple of problems.

Using New Table, I created the Month table according to your example: Months = VALUES('Date Table'[Month]).
The problem arises if, for example, in 2023 we still only have 8 months out of 12. But since, for example, 12 months have already been written in 2022, even with the filter for the year 2023, I see 12 months instead of 8. Is this possible?

And one more problem, the rest of the visualization is not responsive to the Slicer with the Month filter from the Months table. There is probably no other solution, so what you provided is probably the best.

Thank you very much

[Anonymous · Accepted solution]

Hi @Anonymous ,

You can use "Enter data" method to create the month dimension table to replace the original one, please find the details in the attachment.

And for the visuals which not effect by the month slicer, you can consider to create measure with the filter (like 'Table'[month]=selectedvalue('Months'[Month])). Anyway, it need to make the proper change base on the actual situation...

Best Regards