How to add filter for current year data upto previous month

[sf04357]

Hi All

I would like to set a filter to send out the monthly report by subscription but the date filter only allow current year or last x months. It is required to send out an YTD report, but up to previous month.
Anyone know how to set?

Thanks

Vincent

[sf04357]

Thanks Yolo

But I still have found some problem on the display

You see that the table show "0" inin colunm 3 & 4 at 202301, but the actual data is showing in row 2, i.e. 202302. i.e all the data are shift one month, what will be the problem?

[v-xinruzhu-msft]

Hi @sf04357 

I don't know the expression, the data show 0 in the fiest month of the year, what the output you want?

Best Regards!

Yolo Zhu

[sf04357]

For data is shown in row 2 instead of row 1, i.e. the monthly data are shift

[v-xinruzhu-msft]

Hi @sf04357 

1.You can use TOTALYTD() function,

e.g

Measure =
CALCULATE (
    TOTALYTD ( SUM ( Sales[Value] ), 'Date'[Date] ),
    'Date'[Date] < TODAY ()
)

And you can refer to the following link about the function.

TOTALYTD function (DAX) - DAX | Microsoft Learn

2.You can create a measure then put it to the visual filter

Measure =
IF (
    YEAR ( SELECTEDVALUE ( date[date] ) ) = YEAR ( TODAY () )
        && MONTH ( SELECTEDVALUE ( date[date] ) ) < MONTH ( TODAY () ),
    1,
    0
)

Best Regards!

Yolo Zhu

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