Sum of Full Month based on Date Slicer

[newgirl]

Hello! In my report, I have a slicer date that lets the user select one date only. I have the raw data below.

I need a measure that would compute the sum of the amount of the FULL month based on selected date in the date slicer.

Sample Raw data (July full month sum is 2400 while August full month sum is 270).

So if the reader selects July 2, 2022, the measure output should be 2,400.

If the reader selected July 31, 2022, the measure output would still be 2,400.

If the reader selects AUgust 1, 2022, the measure output would be 270.

If the reader selects August 15, 2022, the measure output would still be 270.

fyi that I also have a second table which is the calendar table and it's the calendar table being used for the Date slicer.

Date	Amount
7/1/2022	100
7/2/2022	100
7/3/2022	100
7/4/2022	100
7/5/2022	100
7/6/2022	100
7/7/2022
7/8/2022
7/9/2022	100
7/10/2022	100
7/11/2022	100
7/12/2022	100
7/13/2022	100
7/14/2022	100
7/15/2022
7/16/2022
7/17/2022
7/18/2022	100
7/19/2022	100
7/20/2022	100
7/21/2022	100
7/22/2022	100
7/23/2022	100
7/24/2022	100
7/25/2022	100
7/26/2022	100
7/27/2022	100
7/28/2022	100
7/29/2022	100
7/30/2022
7/31/2022
8/1/2022	15
8/2/2022	15
8/3/2022	15
8/4/2022	15
8/5/2022	15
8/6/2022	15
8/7/2022
8/8/2022
8/9/2022	15
8/10/2022	15
8/11/2022	15
8/12/2022	15
8/13/2022	15
8/14/2022	15
8/15/2022
8/16/2022
8/17/2022	15
8/18/2022	15
8/19/2022	15
8/20/2022	15
8/21/2022	15
8/22/2022	15
8/23/2022
8/24/2022
8/25/2022
8/26/2022
8/27/2022
8/28/2022
8/29/2022
8/30/2022

[Anonymous · Accepted solution]

Hi  @newgirl ,

Here are the columns of the table:

Here are the steps you can follow：

1. Create measure.

Solve1 =
var _select=SELECTEDVALUE('Date'[Date])
return
CALCULATE(SUM('Table'[Amount]),
FILTER(ALL('Table'),
YEAR('Table'[Date])=YEAR(_select)&&MONTH('Table'[Date])=MONTH(_select)))

Solve2 =
var _select=SELECTEDVALUE('Date'[Date])
return
CALCULATE(
    SUM('Table'[Amount]),
    'Table'[Date]>=DATE(
    YEAR(_select),MONTH(_select),1)&&'Table'[Date]<=DATE(YEAR(_select),MONTH(_select)+1,1)-1)

2. Result:

If you need pbix, please click here.

Best Regards,

Liu Yang

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

[amitchandak]

@newgirl ,

new measure =

var _max1 = maxx(allselected('Date'), 'Date'[Date])

var _max = eomonth(_max1,0)

var _min =eomonth(_max1,-1) +1 

return

// if table is joined to date table

calculate(Sum(Table[Qty]), filter(all('Date'), 'Date'[Date]>=_min && 'Date'[Date]>=_max) )

// if table is not joined to date table

calculate(Sum(Table[Qty]), filter(Table, 'Table'[Date]>=_min && 'Table'[Date]>=_max) )

Assumed slicer is on a date table(Connected or disconnected)