Last twelve month total from last day of previous month

[frankhofmans]

Hi all,

i have the following question:

I want to calculate the number of cases (sicness cases) in the last twelve months, from the last day of the previous month (and then twelve months back). So i need the number of cases between october 1st 2019 till september 30th 2020. In when it's november 4th, i need the number of cases between november 1st 2019 and october 31th 2020.

Can someone help me with this question?

Case ID	Start date
112	01-05-2019
118	27-07-2019
243	30-10-2019
167	03-05-2020
770	01-11-2019
345	02-08-2019
122	31-1-2020
451	01-07-2020
732	30-08-2020

So when i want to calculate it from today, i need the cases between october 1st 2019 and september 30th 2020 (6 cases). 

Thanks in advance!

Regards,

Frank

[Icey · Accepted solution]

Hi @frankhofmans ,

Two measures to choose from: 

Measure 1 = 
CALCULATE (
    COUNT ( 'Table'[Case Id] ),
    DATESINPERIOD ( 'Date'[Date], EOMONTH (MAX('Date'[Date]), -1 ), -12, MONTH )
)

Measure 2 = 
CALCULATE (
    COUNT ( 'Table'[Case Id] ),
    FILTER (
        ALLSELECTED ( 'Table' ),
        'Table'[Start date] <= EOMONTH ( MAX ( 'Table'[Start date] ), -1 )
            && 'Table'[Start date]
                >= EOMONTH ( MAX ( 'Table'[Start date] ), -13 ) + 1
    )
)

Best regards

Icey

If this post helps,then consider Accepting it as the solution to help other members find it faster.

[amitchandak]

@frankhofmans ,

Try like this with date table

Rolling 12 = CALCULATE(Table(Table[Case Id]),DATESINPERIOD('Date'[Date],eomonth(MAX(Table[Start Date]),-1),-12,MONTH))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

Please provide your feedback comments and advice for new videos
Tutorial Series Dax Vs SQL Direct Query PBI Tips
Appreciate your Kudos.