Need DAX Correction - to display upto selected months

[ramshoney1225]

Hi 

I have created few DAXs and it was working fine, but i got change in the requriment which i'm not able to do

Old Requirement when selected Year = 2021, Month = March as slicer values then my graph shows as below 

1.) Bar measure show selected Month

2.) Line measure show all the months 

I have 4 Bar measures, I have 4 line measures for reference im sharing of one line measure which i'm having issue in line measure.

Line measure DAX created as below 

Line Measure =
var _tab=ALL('Sheet1'[Month])
var _tab1=ALLSELECTED('Sheet1'[Year])
RETURN
CALCULATE(SUM('Sheet1'[ActualData]),
FILTER(('Sheet1'),
'Sheet1'[month] in _tab &&
'Sheet1'[Year] in _tab1
))

Now new requirement, when selected Year = 2021, Month = March as slicer values then it should show as below 

1.) Bar measure Should show selected Month ( Same as old Requirment)

2.) Line measure Should show upto selected Month ( New Requirment )

so how to alter or modify existing logic for line measure please help

Please find the below sample data for understanding columns ( I have Year, MonthNum, Month )

Year	MonthNum	Month	ActualData	ActualTarget
2021	4	April	111.72	113
2021	5	May	106.54	113
2021	4	April	118.07	113
2021	5	May	117.65	113
2021	4	April	115.1	113
2021	5	May	83.86	113
2021	4	April	118.52	113
2021	5	May	104.5	113
2021	4	April	105.83	113
2021	5	May	93.06	113
2021	4	April	119.56	113
2021	5	May	118.85	113
2021	4	April	119.8	113
2021	5	May	60	113

Thanks,

[v-yanjiang-msft · Accepted solution]

Hi @ramshoney1225 ,

According to your description, you have a slicer of month column, and the axis of the chart is also month, then when you select a value in the slicer, how can not the chart axis be filtered. 

My solution is create another new table.

Table = SUMMARIZE('Sheet1','Sheet1'[Month],'Sheet1'[MonthNum])

The formula of Line Measure is:

Line Measure =
VAR _tab =
    ALLSELECTED ( 'Sheet1'[Year] )
RETURN
    CALCULATE (
        SUM ( 'Sheet1'[ActualData] ),
        FILTER (
            ( 'Sheet1' ),
            'Sheet1'[MonthNum] <= SELECTEDVALUE ( 'Table'[MonthNum] )
                && 'Sheet1'[Year] IN _tab
        )
    )

Then put the Month column in the new table in a slicer, the original month column in the chart axis, the Line measure will show upto selected Month. 

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

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

[amitchandak]

@ramshoney1225 , best is that you use before date range slicer. Other wise you need an independent date table for slicer

You need one or more measures like

//Date1 is independent Date table, Date is joined with Table
new measure =
var _max = maxx(allselected(Date1),Date1[Date])5
return
calculate( sum(Table[Value]), filter('Date', 'Date'[Date] <=_max))

Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI