dax expression help need to get the sum until the date and month selected ?

[ashmitp869]

Hi,

Need help with the dax query:

Selection in report :

I need to calucate the value until the Year and Month Name selected in the filter panel. (i.e <=)

Trying the dax : 

PTD - Billed(Ex-GST) = CALCULATE([Revenue Amount] ,

                         FILTER(SM,SM[Service Group Level 0] ="REVENUE"),

                         FILTER('Date','Date'[Year] <=SELECTEDVALUE('Date'[Year]) && 'Date'[Month] <= SELECTEDVALUE('Date'[Month])

                         )

                       )

Another :

PTD1 - Billed(Ex-GST) = CALCULATE([Revenue Amount] ,

                         FILTER(SM,SM[Service Group Level 0] ="REVENUE"),

                         FILTER('Date','Date'[Date] <= Max(vFactBudgetActuals[Transaction Date]))

                        ,ALL('Date'[Year],'Date'[Month Name])

                         )

[Anonymous · Accepted solution]

Hi @ashmitp869 ,

Please try the following methods and check if they can solve your problem:

1.Create the sample Table.

2.Drag the year, month and Service Group into the three slicer visuals.

3.Create the new measure to calculate filtered Date.

SUM_selected = 
VAR sm_sel = SELECTEDVALUE(SM[Service Group Level 0])
var total_sel = CALCULATE (
        SUM ('Table'[Revenue]),
        FILTER (
            ALL ('Table'),
            'Table'[Year] < SELECTEDVALUE('Table'[Year]) || ('Table'[Year] = SELECTEDVALUE('Table'[Year]))
                && 'Table'[Month] <= SELECTEDVALUE('Table'[Month])
        )
    )
RETURN
IF(sm_sel = "Revenue", total_sel ,BLANK())

4.Drag the measure into the card visual.

5.Select the Year and month in the slicer visual. The result is shown below.

Best Regards,

Wisdom Wu

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