Calculate Last 1 months and next one month

[Anonymous]

Hi Team,

I have a requirement to calculate sum(sales) for the period last one month-current month-next month

For example: I have Date filter which has all the dates from 2017 - currentdate

when user selects 10-sep-2020 it has to get the sales from 1-Aug-2020 till 31-Oct-2020

I also need to show the user the period "1-Aug-2020 till 31-Oct-2020" in the same Table visual. 

Kindly help me how can i calculate this?

[Icey · Accepted solution]

Hi @Anonymous ,

Please check:

period = 
VAR StartDate =
    IF (
        SELECTEDVALUE ( 'Dates'[Date] ) = BLANK (),
        FORMAT (
            EOMONTH ( MINX ( ALL ( 'Table' ), 'Table'[Date] ), -1 ) + 1,
            "d-MMM-YYYY"
        ),
        FORMAT ( EOMONTH ( SELECTEDVALUE ( Dates[Date] ), -2 ) + 1, "d-MMM-YYYY" )
    )
VAR EndDate =
    IF (
        SELECTEDVALUE ( 'Dates'[Date] ) = BLANK (),
        FORMAT ( EOMONTH ( MAXX ( ALL ( 'Table' ), 'Table'[Date] ), 0 ), "d-MMM-YYYY" ),
        FORMAT ( EOMONTH ( SELECTEDVALUE ( Dates[Date] ), 1 ), "d-MMM-YYYY" )
    )
RETURN
    StartDate & " till " & EndDate

Sum = 
VAR StartDate =
    EOMONTH ( SELECTEDVALUE ( Dates[Date] ), -2 ) + 1
VAR EndDate =
    EOMONTH ( SELECTEDVALUE ( Dates[Date] ), 1 )
RETURN
    IF (
        SELECTEDVALUE ( Dates[Date] ) = BLANK (),
        SUM ( 'Table'[Value] ),
        SUMX (
            FILTER (
                ALL ( 'Table' ),
                'Table'[Date] >= StartDate
                    && 'Table'[Date] <= EndDate
            ),
            'Table'[Value]
        )
    )

Best Regards,

Icey

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

[lkalawski]

Hi @Anonymous

Given this data structure and Date table:

You can create a measure:

Sum = 
CALCULATE(Sum(Tbl[Value]), 
    FILTER(ALL('Date'[Date]), 
        'Date'[Date] >= DATE(YEAR(MAX('Date'[Date])), MONTH(MAX('Date'[Date]))-1,1)
        && 'Date'[Date] <= DATE(YEAR(MAX('Date'[Date])), MONTH(MAX('Date'[Date]))+1,30)
        )
    )

In the report, in slicer visual, add Date from the Date table and additionally Card, which will show you the measure result.
Sample result:

_______________
If I helped, please accept the solution and give kudos! 😀

[Anonymous]

@lkalawski Thanks for the reply.

I have the same result from your measure too. 

what i need is, when i select any date in Sep, it should give the same result calculated from 1st Aug - 31st Oct  irrespective of date i select in Sep.

[lkalawski]

Hi @Anonymous 

Can you share your sample of data and relationships between tables? 

I think that problem is with the relationship, but I want to check it.

_______________
If I helped, please accept the solution and give kudos! 😀

[Anonymous]

I cant share the file as i'm unable to create a dummy data.

the relationship between tables Date and sales is 1 to many.  This is how i modified the measure.

Am =

CALCULATE(AVERAGE(Query1[Bill Rate]),

FILTER(ALL('Dim_Date (2)'[Date]),

'Dim_Date (2)'[Date] >= DATE(YEAR(MAX('Dim_Date (2)'[Date])), MONTH(MAX('Dim_Date (2)'[Date]))-1,1)

&& 'Dim_Date (2)'[Date] <= DATE(YEAR(MAX('Dim_Date (2)'[Date])), MONTH(MAX('Dim_Date (2)'[Date]))+1,30)

)

)

[amitchandak]

@Anonymous , With a date table, try a measure like

CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],Startofmonth(dateadd(Sales[Sales Date],-1,month)),3,MONTH))

or

=
var _max1 = maxx(allselected('Date'),'Date'[Date])
var _max = eomonth(_max1,1)
var _min1 = minx(allselected('Date'),'Date'[Date])
var _min = date(year(_min),month(_min)-1,1)
return
calculate([measure],filter(All(DATE), Date[Date] >=_min && Date[Date] <=_max))

That will rollup data into one month: for that refer :https://www.youtube.com/watch?v=duMSovyosXE

[Anonymous]

@amitchandak Thanks Amit, 

I tried the below. Its working, but the problem is when i select any date between 1-sep-2020 to 20-Sep-2020, this should give the same value which is calculated for the period 1-aug-2020 to 31-Oct-2020.  But its not. 

av =

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

var _max = eomonth(_max1,1)

var _min1 = minx(allselected('Dim_Date (2)'),'Dim_Date (2)'[Date])

var _min = date(year(_min1),month(_min1)-1,1)

return

calculate([measure],filter(All('Dim_Date (2)'), 'Dim_Date (2)'[Date] >=_min && 'Dim_Date (2)'[Date] <=_max))