How to show data from first month selected month of slicer

[nmhung49]

I want to show the result as shown above That is, when I select the slicer in any month, it will show the result from January to the month I select.

For example, if I select May, it will show the result from January to May

Attach file

Thank you

[nmhung49]

@shafiz_p 

I want to select value Month from Slicer Calendar[MonthName] of Calendar Table. How do it

Thank you

[shafiz_p · Accepted solution]

@nmhung49  Yes it is possible. Though disconnected table is a very powerful feature. Since you want to use your calendar table. Follow the steps mentioned below.

1. First create a duplicate calendar table. You can use dax or power query to duplicate your calendar table. I named it 'Calendar2'.

2. Need to create a relationship between your original calendar table and duplicate calendar table with "Many to One" Relationship (Many side would be your calendar table and one side would be duplicate calendar table). Cross filter direction would be "Single" and make this relationship inactive.

Keep in mind that, you may face error during relationsip building, due to custom dax column index. I would suggest to change that formula from Index = Calendar[Date].MonthNo to Index = Month(Calendar[Date])

3. Go to your table visual, and remove "Index" column from rows format option and place index from "Duplicate calendar" Table. In this case "Calendar2".

4. Create a simple measure Sum of Value = SUM(Data[Value]).

5. Create the below final measure to show previous all month from first to till slicer selection.

Sum of Value Prev All Month =

VAR _maxDate = MAX('Calendar'[Date])

VAR _selectedMonth = SELECTEDVALUE('Calendar'[Index], MAX('Calendar'[Index]))

VAR _PrevDates =

        DATESINPERIOD(

            calendar2[Date],

            _maxDate,

            -_selectedMonth,

            MONTH

        )

VAR _result =

    CALCULATE(

        [Sum of Value],

        REMOVEFILTERS('Calendar'),

        KEEPFILTERS(_PrevDates),

        USERELATIONSHIP('Calendar'[Date], calendar2[Date])

    )

RETURN

_result

Place the above measure in the table visual and remove any other measure or value column. You will get your desired result.

In this way, you can use your calendar table month in the slicer and able to slice data from first month to till your slicer selection.

If this solved your problem, Please accept it as a solution!!

[shafiz_p]

Hi @nmhung49  You can solve this problem using a disconnected table. Create a disconnected table of month. See image below:

Create measure : 

Sum of Value = SUM(Data[Value])

Create another measure :

Total Value =

VAR _max = SELECTEDVALUE('Index table'[Index], MAX('Index table'[Index]))

RETURN

IF(SELECTEDVALUE('Calendar'[Index]) <= _max, [Sum of Value])

In table visual, Place 'Calendar'[Index] in row and in value section place [Total Value] measure. Create a slicer with month from 'Index table'. You are done. Sort month by index number.

Output:

Hope this helps!!

If your problem solved, please accept it as a solution!!