Calculate data in the period in the past with a delay

[dtataev]

How to calculate in DAX( or another way in Powerbi) the "count of loans" that were received in the period between 6 and 18 months from the selected month?

For example, if I select June 2022, I wan to see  how many loans were received in the period between 6 and 18 months before that.( In this case, it would be the full year of 2021), and if I would select another month, then the calculation would shift accordingly.

I have a dates table, and table with Loans and received dates for them for simplicity. 

Would you be able to share any ideas? I could not find a working solution. 

Thank you!

[Anonymous · Accepted solution]

Hi  @dtataev ,

I created some data:

Table:

Date:

Here are the steps you can follow：

1. Create calculated column.

month_year =
FORMAT(
    'Date'[Date],"mmmm")&" "&YEAR('Date'[Date])

Rank =
RANKX(
       ALL('Date'),'Date'[Date],,ASC)

2. Create calculated table.

Slicer =
SUMMARIZE(
    'Date',
    'Date'[month_year],"Index",
    MINX(
        FILTER(ALL('Date'),
        'Date'[month_year]=EARLIER('Date'[month_year])),[Rank]))

3. [month_year] – Column tools – Sort by column – [Index]

4. Create measure.

Flag =
var _select=
SELECTEDVALUE(Slicer[month_year])
var _maxdate=
EOMONTH(
MAXX(
    FILTER(ALL('Date'),'Date'[month_year]=_select),[Date]),0)
var _6Month=
EOMONTH(_maxdate,-6)
var _18month=
EOMONTH(
    _maxdate,-18)
var _first18month=
DATE(
    YEAR(_18month),MONTH(_18month),1)
return
IF(
    MAX('Table'[Date]) >= _first18month&& MAX('Table'[Date]) <=_6Month,1,0)

Value_all =
SUMX(
    ALLSELECTED('Table'),[Value])

 5. Place [Flag]in Filters, set is=1, apply filter.

6. Result:

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]

@dtataev , Try a measure with help from date table

Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],eomonth(MAX('Date'[Date]),-6) ,-12,MONTH))

Rolling Months Formula: https://youtu.be/GS5O4G81fww

You can also consider window function

Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc

[dtataev]

Thank you, it worked if I select June 2022, it shows data for the whole year of 2022. BUT it doesn't work if I select May or any other month. The numbers do not sum correctly ( more than there should be).

How can it be?