Total Claims count in 12 rolling periods

DAX DAX Calculations
1

Claims - 12 month rolling dummy data.xlsx (5.0 MB)
Claims - forum 28_09_22.pbix (194.5 KB)

Hi all.

I hope you are all well .

Could I please for help in following issue.
I have to create measure which gives “claims count in the last 12 month” based on chosen period.
I should get 5207 claims when choosing Period “2022-02” and trying to use different approaches but no success . ( I am not sure if I should create " end period" column for this purpose ?)
I have attached raw data in PBI file . Would be so grateful for your help which could possible lead to solve the problem.

Many thanks,

Iwona

2

Hi @Iwona,

Few observations:

  • the first date in your date table was set to 4-7-2021 BUT the first date in your fact table is 5-1-2021, so I’ve changed that into 5-7-2020.
  • you haven’t marked your date table, as a date table.
  • your date table includes *ID columns, which you can use as offsets.

Created this measure

R12 =
VAR PeriodEnd = SELECTEDVALUE( 'Dates Table'[MonthID], MAX('Dates Table'[MonthID]) )
VAR PeriodStart = PeriodEnd -11
RETURN

CALCULATE( COUNTROWS( 'Claims 21/22' ),
    FILTER(  ALL('Dates Table'),
        'Dates Table'[MonthID] >= PeriodStart &&
        'Dates Table'[MonthID] <= PeriodEnd
    )
)

With this result

image
image353×542 14.2 KB

Here’s your sample file
Claims - forum 28_09_22.pbix (204.7 KB)

I hope this is helpful

3

@Melissa ,

happy to see your name :slight_smile: and grateful for your support,

I set this date following our company calendar to get correct 4-4-5 fiscal calendar.
image

IS this wrong ?

Iwona

4

Hi @Iwona

Your date table should span full years and include all days, from the first to the last date in your data model. That was not the case.

However the bonus here is that with this type of date table all years should have 364 days… that means you can calculate a prior year’s start date…

5

@Melissa ,

Could you please , tell me why you put 5-7-2020 not 1-1-2020 in this case ? How should I figure out what data I should use there. What are the rules behind it ?
Many thanks,

Iwona

6

Just subtract 364 from 4-7-2021 to get the prior years start date
Full years in your fiscal date table consist of 364 days

7

@Melissa

As always many thanks for all :slight_smile:

Iwona