cancel
Showing results for
Did you mean:
Highlighted Helper II

## Last twelve month total from last day of previous month

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).

Regards,

Frank

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted Community Support

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.

2 REPLIES 2
Highlighted Super User IV

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.

Tutorial Series Dax Vs SQL Direct Query PBI Tips

Proud to be a Super User!

Highlighted Community Support

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.  