How to do Sum between

[samnaw]

Hello:

I have 3 columns:

its looks like this:

r12from: 32021 means (MM-YYYY or 03-2021)

r12to: 22022 means (02-2022) 

So I want to do something like this:

I have a "measure1" that spits out a date based on some calculation. measure1 is a date (i.e 13-Nov-21) 

SUM(Sales) all rows between 03-2021 and 02-2022 based on "measure1" 

So if measure1 is 13-Nov-21 so I want to SUM(Sales) all dates between 03-2021 and 02-2022. 

[v-jayw-msft · Accepted solution]

Hi @samnaw ,

Create new columns to transform the date.

r12from column= 
var _rightyear=RIGHT(('Table'[r12from]),4) 
var _leftmonth=LEFT(('Table'[r12from]),1) 
var _date=_rightyear&"/"&_leftmonth&"/"&1 
return _date

r12to column= 
var _rightyear=RIGHT(('Table'[r12to]),4) 
var _leftmonth=LEFT(('Table'[r12to]),1) 
var _date=_rightyear&"/"&_leftmonth&"/"&1 
return _date

Then create a measure like below:

measure =
VAR _1 =
    CALCULATE (
        SUM ( table[sales] ),
        FILTER (
            ALL ( table ),
            table[date] <= SELECTEDVALUE ( table[r12from column] )
&& table[date] >= SELECTEDVALUE ( table[r12to] )
        )
    )
RETURN
    IF ( measure1 = DATE ( 2021, 11, 21 ), _1, BLANK () )

Best Regards,

Jay