DAX calculation of moving annual total - split/drill to month

[Anonymous]

Hi Power BI Users,

The pbix-file can be found at this link

I have followed sqlBI's time-pattens approached to calculate moving annual total by including a SequentialDayNumber in the date table (in my solution called "Calendar". My date column in the calendar table is called "Calendar Date" (link: http://www.daxpatterns.com/time-patterns/).

I get the correct result for "Number of Claims MAT TY", which is 6287. I would like to show the trend (time), thus I want to split MAT TY by month.

My issue: When I add my calendar to the graph I only see today's date. I would like to be able to drill down to month (or date). Having 365 days.

My calculation of MAT TY are the following:

Number of Claims MAT TY 3 =
CALCULATE (
    [Number of Claims];
    FILTER (
        ALL( 'Calendar' );
        'Calendar'[SequentialDayNumber] > CALCULATE(MAX ('Calendar'[SequentialDayNumber]);FILTER('Calendar';'Calendar'[Calendar Date] = TODAY() )) - 365
             && 'Calendar'[SequentialDayNumber] <= CALCULATE(MAX('Calendar'[SequentialDayNumber]);FILTER('Calendar';'Calendar'[Calendar Date] = TODAY())
    )
)
)

Hope someone can help.

In the below picture I would like to show the graph in the buttom as the one in the top.

In the top I have manual filtered the data based on the measure sum("Number of Claims"), whereas the one in the bottom has the "Number of Claims MAT TY 3" included.

Bests,

Line

[Phil_Seamark]

HI @Anonymous

To me the measure looks like it is doing what it has been asked to do.  What number would you expect to see when you drill down to a particular month?

[Anonymous]

Sorry, it is the graph in the bottom that has the messerne included. For the one in the top I have manual filtreres the graph by using the visual filters.

[Phil_Seamark]

Hi @Anonymous

I understand it is the graph in the bottom that has the measure.  However I'm still keen to understand what you want to see when you drill down to a partucluar month?  eg if you drill down to just January, do you just want to see the [Number of Claims] just for January?

[Anonymous]

Hi Phil,

If I did drill down to to next level (months) I would like to see the 6287 spilt pr month, e.g. 518 for may 2016, 574 for june 2016 and so on.

Can I not expect to do this with this measure and how i have made the calculation?